EXPLAIN - the query plan

How do you know whether the Library uses a catalog or laboriously walks shelf by shelf? You ask for the query plan with the EXPLAIN command.

How it works

Add

EXPLAIN

before any

SELECT

query:

1EXPLAIN SELECT * FROM ksiazki WHERE tytul = 'Iliada';

MySQL will not run the query but will show how it intends to run it - like peeking at the librarian's plan before he sets off among the shelves.

What we read in the plan

EXPLAIN returns a table with several columns. To start, two matter most:

• type - the access method.

  ALL

  means a full table scan (bad!), while

  ref

  or

  const

  means an index is used (good!).
• key - which index was used. If you see

  NULL

  here, no catalog helped and the database reads everything.

An example of reasoning

Run EXPLAIN without an index on

tytul

- you will see

type: ALL

,

key: NULL

. The database scans every scroll.

Create the

idx_tytul

index, run EXPLAIN again - now

type: ref

, and

idx_tytul

appears in

key

. The Library looked into the catalog instead of wandering the shelves.

What is all this for

EXPLAIN is your diagnostic tool. When a query runs slowly, check its plan first. If you see a full scan where you filter by a column - that is a sign an index would help. A good scribe always checks the plan before blaming the Library for being slow!