MySQL in index Condition pushdown (ICP) optimization

An optimized way of querying based on an index that was supported at MySQL 5.6. The previous MySQL database version does not support the ICP, and when the index query is made, the records are first looked up according to the index, and then the records are filtered based on the Where condition. After the ICP is supported, the MySQL database will determine if the where condition is filtered while the index is being fetched, that is, by placing some of the filtering operations at the storage engine layer. Under some queries, you can significantly reduce the fetch of records from the upper layer of SQL to improve overall performance

ICP optimization supports range,ref,eq_ref,ref_or_null types of queries that currently support the MyISAM and InnoDB storage engines. You can see the using index condition hint in the extra column when the optimizer chooses ICP optimization

Join a table with a federated index (ZIP_CODE,LAST_NAME,FIRSET_NAME) and query statements as follows

 select  *    people  where  Zipcode=   " 95054    

For the above statement, the database can use the index to locate records with ZipCode equal to 95054, but the index does not help LastName like '%etrunia% ' and address like '%main street% ' of the Where condition. If ICP optimization is not supported, the database needs to first remove all records with a zipcode equal to 95054, and then after filtering where the two conditions

If the ICP is supported, when the index is removed, the Where condition is filtered, and then the record is fetched. This will greatly improve query efficiency. Of course, where you can filter the conditions when you want to change the scope of the index can be overwritten

SELECT *  fromSalariesWHERE(from_datebetween "'1986- on- on' and'1995- on- on') and (salary between 38000 and 40000);

You can see that extra has a using index condition hint, but why is the idx_s index here used for ICP optimization? Because the primary key of this table is the (emp_no,from_date) federated Index. Therefore, the idx_s index contains the from_date data, so it can be used to optimize the way

Compare execution time when MRR is turned on

You can see the performance tips after you turn on MRR 400%

Index Condition pushdown (ICP) optimization in MySQL