The single most important concept.
WHAT AN INDEX IS
A separate structure letting the database find rows without reading every one.
WHAT IT RESEMBLES
The index of a book: sorted keys pointing at locations.
WHAT WITHOUT ONE MEANS
A full scan, reading every row.
WHY THAT IS FINE ON SMALL TABLES
A thousand rows scan instantly.
WHY IT IS FATAL ON LARGE ONES
A million rows do not, and the cost grows with the table.
WHAT THAT PRODUCES
An application that works for a year then degrades suddenly.
WHAT STRUCTURE MOST INDEXES USE
A balanced tree, giving fast lookup and ordered traversal.
WHAT THAT SUPPORTS
Exact matches Ranges Sorting Prefix matches on text
WHAT IT DOES NOT SUPPORT
Matching a pattern with a leading wildcard.
WHY
There is no starting point to search from.
WHAT A COMPOSITE INDEX IS
One covering several columns, in order.
WHY THE ORDER MATTERS
It can be used for the first column alone, or the first two, but not the second alone.
WHAT A COVERING INDEX IS
One containing every column a query needs.
WHY IT IS FAST
The table itself is never read.
WHAT EVERY INDEX COSTS
Space, and time on every write.
