We have query_cache_type= DEMAND but still a single query that contains multiple joins with four tables uses “checking query cache” state… strange thing is that it only happens for these four tables. Would there be any reason for it.
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20) NOT NULL AUTO_INCREMENT, a varchar… (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key_len… see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It…
Comment: High Rate insertion with MySQL and Innodb
March 29, 2012 By 16 Comments
… million, the table has parent child relationship and the engine type is InnoDB. The process is very slow, it first checks either parent…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…-queries-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size… and I have to re-restart the server. I’ve checked the security aspect and not an attack, will have any…
Post: Can you Trust CHECK TABLE ?
September 11, 2008 By 6 Comments
… this: mysql> repair table a3; +———+——–+———-+———-+ | Table | Op | Msg_type | Msg_text | +———+——–+———-+———-+ | test.a3 | repair | status | OK | …obviously wrong while CHECK TABLE is not reporting any error Why ? Because CHECK TABLE only looks at MyISAM…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…> check table test; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> check table test; +———–+——-+———-+———-+ | Table | Op | Msg_type | Msg_text | +———–+——-+———-+———-+ | test.test | check | status | OK | +———–+——-+———-+———-+ 1 row in set (0.69 sec) First run is check table in…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
….cc as mysql_frm_type(). What this code snippet does is: open the FRM read 10 bytes (“header”) check if it’s a view by doing a string compare for “TYPE=VIEW\n” being the first bytes… instead of the normal binary format FRM. some legacy check for a generic table type (I think, I haven’t gone back into…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…_retriesblogpercona.commanual slave_type_conversionsblogpercona.commanual Slow_…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…_time: 2008-04-24 00:54:18 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options…_time: 2008-04-24 00:37:48 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options… (0.11 sec) As you can see table which uses BIT(1) column type takes same space as the one which uses…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…data to yet another table though why it does not work for multiple group remains question to check with MySQL Optimizer …Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key_len: …
