…example: mysql> show create table t; [...] KEY `key_name` (`name`), KEY `key_name_2` (`name`,`age`) The redundant index is on the ‘name’ column. To take benefit from a composite index MySQL…or redundant indexes can hurt the performance. Checking the schemas periodically to catch this …
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… | +———————-+———-+ | starting | 0.000075 | | checking permissions | 0.000004 | | checking permissions | 0.000004 | | Opening tables | 0.000021 | | System lock…take ages as soon as there is competition for the lock. Of course, the question was: why did MySQL…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…mysql> explain SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347); +—-+————-+——–+——-+—————+——+———+——+——+————-+ | id | select_type | table…
Comment: Upgrading MySQL
January 17, 2010 By 24 Comments
…) and then upgrade to the newer binaries and run mysql_check followed by mysql_upgrade. That works pretty well as long as you… dump/load takes ages. Then again none of this is really necessary: ALTER TABLE ENGINE=INNODB rebuilds the table under 5.1 and MySQL is… if somehow mysql_check (5.1) could be run on an old version and give some sort of idea of which tables may…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… I would have something like AGE=18 AND STATE=’CA’ …MySQL gets a “row pointer” which can be primary key value (for Innodb tables) physical file offset (for MyISAM tables…look up the data (and check remaining portion on WHERE clause …could do. Indeed the query takes 360ms Note also the estimated values…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…FROM people where gender=’m’ and age between 18 and 28 and country…most cases these will be specified. It takes a good look at queries actually…index however will not be helpful to check c=5 (unless it is index …table. Here is example when ORDER BY is done by second table which requires filesort: mysql…
