icp(acronym of index condition pushdown), which used when index(part of a multicolumn index) can be use to filter out some(enormous maybe) on storage level
- the output of
EXPLAINcound display some information to indicate user, suck as key_len and key, especially in Extra column which will dislay
using index condition
- I think that only multicolumn index could make use of this method
- even there exist only one where condition, the icp method would happened, becuase it did
- need satisfy leftmost prefix
- For InnoDB tables, ICP is used only for secondary indexes, The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
- Conditions that refer to subqueries cannot be pushed down.
- it depends on optimizer and analyse according to statistic to decide where use icp on specific query