Indexing Optimization in Practice
Covering Index
A Covering Index occurs when an index contains all the data required for a specific query. Because the index has all the answers, MySQL doesn't need to perform a "Key Lookup" on the clustered index.
-- Composite Index on (name, age)
SELECT name, age FROM users WHERE name = 'John';
-- Covering Index ✅! All data is in the secondary index leaf.
SELECT * FROM users WHERE name = 'John';
-- Non-covering ❌. Still needs to perform an I/O to get the rest of the columns.
Speed Advantage: In the EXPLAIN output, look for Using index in the Extra column. It means zero lookups into the actual table.
The Leftward Prefix Rule
For a composite index on (a, b, c), MySQL sorts the index primary by a, then b, then c. If you skip a column in your query, you lose the efficiency of the subsequent columns.
WHERE a=1$\rightarrow$ ✅ UsesaWHERE a=1 AND b=2$\rightarrow$ ✅ UsesaandbWHERE b=2$\rightarrow$ ❌ Skipa. Full scan / Index skip scan.WHERE a=1 AND c=3$\rightarrow$ ⚠️ Usesa, but cannot usecbecausebwas skipped.
Index Condition Pushdown (ICP)
Introduced in MySQL 5.6, ICP moves part of the filtering logic from the "Server Layer" down into the "Storage Engine Layer."
-- Composite Index on (name, age, position)
SELECT * FROM users WHERE name LIKE 'J%' AND age = 22;
- Without ICP: Engine returns all names starting with "J." The Server then filters them for
age=22. Highly inefficient if there are many "J" names. - With ICP: The engine checks
age=22inside the index itself while scanning the "J" range. It only returns the rows that match both criteria, drastically reducing the number of clustered index lookups.
Common Index-Killing Mistakes
| Mistake | Example | Rationale |
|---|---|---|
| Functions on Index Column | WHERE YEAR(date) = 2024 |
Functions destroy the sorted order of the index. |
| Implicit Type Conversion | WHERE string_col = 123 |
MySQL must convert the column to a number for comparison, negating the index. |
| Leading Wildcards | WHERE name LIKE '%son' |
B+ Trees sorted by prefix. % at the start requires a full scan. |
| OR with Non-Indexed Col | WHERE id=1 OR other=2 |
Unless both have indexes, MySQL will revert to a scan. |
| Inequality on Left Prefix | WHERE a > 1 AND b = 2 |
Range conditions (>, <) break the sorting for subsequent index columns (b). |
Deep Technical Insights
Choosing Cardi-nality
Cardi-nality refers to the number of unique values in a column. An index on Gender (High repetition, low cardinality) is almost always ignored by the optimizer because it's faster to just read the whole table. Always index columns with high cardinality (e.g., UserID, Email).
The "Explain" Tool
Always verify your theories with EXPLAIN.
type: const/eq_ref: Peak performance.type: ref: Good.type: range: Acceptable.type: index/ALL: Warning. You are likely performing an expensive scan.