Understanding Index Condition Pushdown (ICP) in MySQL and Its Impact on Query Cost

Index Condition Pushdown (ICP), also known as Index Condition Pushdown, is a MySQL optimization where filter predicates that cannot be fully satisfied by an index are pushed down to the storage engine, allowing the engine to filter rows before a costly back‑table lookup.

Consider a composite index idx_test(birth_date,first_name,hire_date) and the query: SELECT * FROM employees WHERE birth_date >= '1957-05-23' AND birth_date <= '1960-06-01' AND hire_date > '1998-03-22'; MySQL first uses the index to locate rows matching the birth_date range (returning ~100 000 rows), then pushes the hire_date > '1998-03-22' condition to the storage engine, reducing the set to about 1 000 rows before performing the back‑table lookup.

In the experiment the Employees Sample Database is loaded, the composite index is created with: ALTER TABLE employees ADD INDEX idx_test(birth_date,first_name,hire_date); The query is executed twice: once without a hint and once with FORCE INDEX(idx_test) . The plain EXPLAIN shows that without the hint the optimizer chooses a full table scan (type=ALL) and does not use the index. With the hint the plan shows type=range and Using index condition , indicating ICP is active.

Slow‑log measurements confirm the performance difference: the full scan processes ~300 000 rows in 0.15 s, while the indexed execution scans ~141 000 index rows and only 1 065 back‑table rows, completing in 0.037 s.

The optimizer selects plans based on a cost model that adds I/O and CPU components. For the full scan the estimated cost is 60 725 (IO = 929, CPU = 59 796). The indexed plan’s reported cost is 197 669.81, because the optimizer treats the entire index scan row count (141 192) as back‑table rows, inflating the I/O and CPU costs for the back‑table phase.

When the real back‑table row count (1 065) is used, the true cost would be IO = 1 065 and CPU = 213, far lower than the full‑scan cost. However, because the optimizer’s cost calculation ignores the reduction provided by ICP, it incorrectly prefers the full table scan.

Conclusion: ICP can dramatically reduce execution time by cutting back‑table accesses, but MySQL’s current optimizer cost model does not account for this benefit during plan selection, leading to suboptimal choices.