What this topic covers
23 subtopics- Composite indexes5
- When indexes are ignored5
- B+ trees3
- Clustered index3
- Trade-offs3
- EXPLAIN2
- Index structures2
- Index types2
- Indexes created by constraints2
- Optimisation workflow2
- Optimiser2
- Selectivity2
Show all 23 subtopics
- Sorting2
- Access paths1
- Basics1
- Block access1
- Covering indexes1
- Joins1
- Pagination1
- Partitioning1
- Secondary indexes1
- Unique index1
- When indexes help1
- Beginner 15
- Intermediate 15
- Advanced 15
About this topic
An index is a separate, sorted structure (in MySQL and MariaDB, a B+ tree) that lets the database find rows without scanning the whole table. Good indexes turn seconds into milliseconds; bad ones slow every write and are ignored by the optimiser. Senior interviews ask why a query does not use an index, how to order composite indexes and how to read EXPLAIN; GATE asks for B+ tree orders and block-access counts.
Free preview
Sample questions
Try each one first, then open the answer for the explanation and why the other options are wrong.
What is a database index?
Show answer and explanation
Explanation
Like the index at the back of a book, it lets the database jump to the right pages. Lookups become logarithmic instead of linear.
Why the other options are wrong
- AInsertion order is not an index.
- BIndexes hold keys and pointers, not a backup.
- CThat is NOT NULL.
orders has an index on order_date. Why can WHERE YEAR(order_date) = 2026 not use it efficiently, and what is the fix?
Show answer and explanation
Explanation
The index is sorted by order_date, not by YEAR(order_date), so the database would have to compute the function for every row. A range on the bare column is "sargable" and uses the index.
Why the other options are wrong
- ADates index well.
- BIt is allowed, just slow.
- DDISTINCT is unrelated.