Senior LevelSQL & MySQL
How does Index Condition Pushdown (ICP) optimize composite index filtering?
❓ Interview Prompt: Explain Index Condition Pushdown (ICP), pushing WHERE evaluation down to the storage engine layer, and reducing table record reads.
Index Condition Pushdown (ICP) is a MySQL optimization that evaluates parts of the 'WHERE' clause directly at the storage engine layer (InnoDB) using index columns:
Without ICP vs With ICP:
•
Without ICP: Storage engine reads index node $\rightarrow$ fetches full table row from disk $\rightarrow$ returns to Server layer for 'WHERE' evaluation.
•
With ICP: Storage engine evaluates index 'WHERE' conditions first. If false, it drops the row immediately without fetching the full table row from disk!
Code Implementation Example
sql
-- Query benefiting from Index Condition Pushdown (ICP)
-- Composite Index on (zip_code, last_name)
EXPLAIN SELECT * FROM address_book
WHERE zip_code = '90210' AND last_name LIKE '%son%';
-- Extra displays: Using index condition💡Inspecting Index Condition Pushdown execution in EXPLAIN.
🎯 Interview Key Takeaway
ICP reduces disk I/O by evaluating index WHERE conditions at the storage engine layer before reading full table rows.
#MySQL#ICP#Index Condition Pushdown#Optimizer#Senior
Back to all SQL & MySQL Questions→