15 Oct 2014 Index Condition Pushdown (ICP) in MySQL 5.6
Before Mysql 5.6 indexes had some limitations like the range comparisons, if the part of the index used in a range comparison the part of the index after the range scans cannot be used by where conditions so mysql retrieves all rows which met only the first part of the index condition and then apply the where condition.
For example, if you have index like (first_name, last_name), and the query condition is something like: WHERE first_name LIKE “A%” AND last_name LIKE “%B%”. MySQL will retrieve all the rows that start with letter “A”, then apply the other part of condition at the MySQL server layer . But this will take longer time than filtering the index rows directly, hence Index Condition Pushdown.
Index Condition Pushdown (ICP) is a feature introduced in mysql 5.6. MySQL pushdown the “where” condition to the storage engine level. It applies the “where” condition if the condition can be applied from the index directly.
Using Sakila database to test this feature with addition of custom index:
mysql>SHOW CREATE TABLE film;
CREATE TABLE `film` (
`film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` text,
`release_year` year(4) DEFAULT NULL,
`language_id` tinyint(3) unsigned NOT NULL,
`original_language_id` tinyint(3) unsigned DEFAULT NULL,
`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
`length` smallint(5) unsigned DEFAULT NULL,
`replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
`rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
`special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`film_id`),
KEY `idx_title` (`title`),
KEY `idx_fk_language_id` (`language_id`),
KEY `idx_fk_original_language_id` (`original_language_id`),
KEY `icp` (`film_id`,`title`),
CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
The following query is supposed to retrieve 661 row from the film table, this query will use the index to retrieve 900 rows out of 1000 which their film_id > 100 and then apply the where condition on the server level.
mysql> select * from film FORCE INDEX (icp) where film_id>=100 and title like “%A%”; 661 rows in set (0.13 sec)
mysql> explain select * from film FORCE INDEX (icp) where film_id>=100 AND title like "%A%"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: icp
key: icp
key_len: 2
ref: NULL
rows: 900
Extra: Using where 1 row in set (0.00 sec)
The rows column in explain command is only an estimate of the rows returned by the query but by issuing the SHOW STATUS command it turns out that Handler_read_index returns indeed the whole 900 command and then applied the where condition on the server level.
mysql> SHOW STATUS; | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 901 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 43 |
After installing another instance of mysql 5.6 the previous query will use the index condition pushdown feature:
mysql> explain select * from film FORCE INDEX (icp) where film_id>=100 and title like "%A%"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: icp
key: icp
key_len: 2
ref: NULL
rows: 900
Extra: Using index condition
The Extra field showed Using index condition which means that mysql used the optimization feature of mysql 5.6 .
mysql> SHOW STATUS; | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 661 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 32 |
SHOW STATUS shows that mysql retrieve only the 661 rows from the storage engine which means it did push the where condition down to the storage engine, this considered as a huge optimization feature in the MySQL 5.6.