MySQL EXPLAIN Command
From Shrubbery
- The EXPLAIN command shows the query plan for a given query. It can be used to analyze slow running queries.
See:
Basic use:
mysql> EXPLAIN ...query...;
This will produce a number of rows showing each phase of the query plan.
The type column:
| Value | Description |
|---|---|
| ALL | Full table scan. Watch out for this one on large tables, as it usually means there is a missing index. |
| index | Full index scan. While not as bad as ALL, this can be a problem if the index is large. |
| range | An index is being used to select specific rows.
... WHERE A.INDEXED_COL < 'some constant' |
| eq_ref | This is a direct lookup from one table to another using a foreign key / primary key.
... WHERE A.B_ID = B.ID |
| ref | This is a direct lookup from one table to another using a foreign key / partial primary key.
... WHERE A.B_K1 = B.K1 |
| ref_or_null | This is a direct lookup from one table to another using a foreign key / partial primary key or a null value.
... WHERE A.B_ID = B.ID OR A.B_ID IS NULL |
