… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… is understanding when exactly MySQL is going to use index and how it is going to do them. So if you have table people with KEY(age… point. So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…KEY `key_name_2` (`name`,`age`) The redundant index is on the ‘name’ column. To take benefit from a composite index MySQL doesn’t need to use…when both long and short index might be good for the query execution. Redundant suffixes on clustered index InnoDB uses a clustered index…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… DEFAULT NULL, `role_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `role_id_note` (`role_id`,`note…MySQL is conservative about “Using index” -in most cases it will be the right solution because our SELECT will only be faster when… are sometimes not enough. Do you want to know more about the MySQL 5.6 …
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…not to use auto_increment but use certain partitioned sequences, for example you can have 256 growing sequences (with high byte used…when it comes to auto_increment columns, which is however completely separate problem which can be fixed in MySQL…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…when table has to be traversed in both directions for different queries. This will use fast primary key for some queries and use key K as covering index for lookup in other direction. For Innodb Table id2 is not…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…not allow MySQL optimizer to use second index and hence index merge, what does it turn to ? It is not combined index but single index…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… which can use index can also use prefix of the new index are they ? It turns there are special cases when this is not the case… to use FORCE INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index changes from mk-duplicate-key-checker key…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…key_len: 2,2,4 ref: NULL rows: 8100 Extra: Using intersect(user_type,status,parent_id); Using where; Using index; Using…index, our merged indexes are covering for the query, so that’s also good. When we ran it, however, it took 3 seconds. Not…, if an index isn’t there, MySQL can’t attempt to use it, …
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… idx_first index as a relevant choice (possible_keys: NULL). What do we get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first… all. But why? Simply because all access patterns are not equal. When we are doing a full table scan, we are doing…
