Where to put them.
WHAT TO INDEX
Columns used in filtering conditions Columns used for joining Columns used for ordering Foreign keys
WHY FOREIGN KEYS SPECIFICALLY
They are joined constantly, and deletes on the parent scan the child without one.
WHAT TO ESTABLISH BEFORE ADDING ONE
Which queries are actually slow.
HOW
The slow query log, and the execution plan.
WHAT SELECTIVITY MEANS
How many distinct values a column has.
WHY IT MATTERS
An index on a column with two values rarely helps.
WHAT ORDER TO PUT COLUMNS IN A COMPOSITE INDEX
Equality conditions first, then ranges, then sorting.
WHY THAT ORDER
The index can only be followed while conditions are exact.
WHAT NOT TO DO
Index every column Create an index per query without checking existing ones Duplicate an index that an existing composite already covers
WHY DUPLICATES MATTER
They cost writes and space while adding nothing.
HOW TO FIND UNUSED INDEXES
Statistics on index usage, where the database provides them.
WHAT TO DO WITH THEM
Remove them, after confirming over a full business cycle.
WHY A FULL CYCLE
Month-end reporting uses indexes nothing else does.
WHAT TO TEST BEFORE ADDING ON A LARGE TABLE
The time it takes, on a copy.
