Indexes for MySQLs
MySQL indexes are structures, mostly B-trees in InnoDB, that let the server find rows without scanning the whole table. This unit in the Performance and Logic stage covers primary, unique, composite and covering indexes, reading EXPLAIN output, and writing queries that can actually use the indexes you create.
Stage: Performance and Logic · Part of the MySQL roadmap · Last reviewed 19 November 2026
What to learn in Indexes
- Create a single-column and a composite index and explain the leftmost prefix rule
- Read EXPLAIN to see the chosen key, the type and the estimated rows
- Write conditions that can use an index instead of wrapping columns in functions
- Recognise a covering index that answers a query without touching table rows
- Weigh faster reads against slower writes and extra storage
Indexes interview questions
Why might MySQL ignore an index on a column?
Common reasons are applying a function to the column in WHERE, a leading wildcard in LIKE, a type mismatch between column and value, or a low-selectivity column where a scan is cheaper. The optimiser then picks a full table scan.
What is a composite index and how does column order matter?
A composite index covers several columns in a defined order. MySQL can use it for queries that filter on a leftmost prefix, such as the first one or two columns. An index on (a, b) helps filtering by a, or by a and b, but not by b alone.
Where Indexes fits in the MySQL roadmap
Indexes belongs to the Performance and Logic stage (beginner level). Go further.
Other units in this stage
Back to the full MySQL roadmap