Understanding how a query will run.
WHAT IT DOES
Prefix any SELECT with EXPLAIN and the database reports its plan rather than running it.
WHAT TO LOOK AT
The type column. ALL means a full table scan, reading every row. On a large table that is the problem. The key column. NULL means no index is being used. The rows column. An estimate of how many rows will be examined.
THE PATTERN THAT INDICATES TROUBLE
type of ALL, key of NULL, and a large rows estimate.
That query will get slower as the table grows, and an index on the filtered column is the fix.
WHAT GOOD LOOKS LIKE
An index named in the key column, and a small rows estimate.
USING IT
When a page is slow, find the slow query with a monitoring plugin, then EXPLAIN it.
That tells you whether an index will help before you add one.
AFTER ADDING AN INDEX
EXPLAIN again. The key column should now name your index and the rows estimate should fall sharply.
If it does not, the index is on the wrong column.
