Understanding what the database will do.
WHAT A PLAN SHOWS
How the database intends to satisfy a query.
HOW TO SEE IT
Prefix the query with the explain keyword.
WHAT TO LOOK AT FIRST
Whether an index is used.
WHAT TO LOOK AT SECOND
How many rows are examined.
WHAT EXAMINING FAR MORE ROWS THAN RETURNED INDICATES
A missing or unusable index.
WHAT ACCESS TYPES MEAN, ROUGHLY
- Reading a single row by key: best
- Reading a range from an index: good
- Reading the whole index: acceptable on small tables
- Reading the whole table: usually the problem
WHAT A TEMPORARY TABLE IN THE PLAN MEANS
The database must materialise intermediate results.
WHAT A FILE SORT MEANS
Sorting cannot be satisfied by an index.
WHY BOTH MATTER
They consume memory, and spill to disk when large.
WHAT MAKES AN INDEX UNUSABLE
A function applied to the column A type mismatch between column and value A leading wildcard Conditions joined in ways the optimiser cannot use
WHAT TO DO ABOUT A FUNCTION ON A COLUMN
Rewrite the condition so the column stands alone.
WHAT ANALYSING A QUERY ADDS OVER EXPLAINING IT
Actual figures rather than estimates.
WHY THAT MATTERS
Estimates can be badly wrong when statistics are stale.
WHAT TO DO ABOUT STALE STATISTICS
Update them.
