SQL and MySQL course Β· Module 9: Indexes and normalization

EXPLAIN - the query plan

2 min read Β·
In this lesson4

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:

EXPLAIN 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!

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is the EXPLAIN command before a SELECT query used for?

  2. 2. In an EXPLAIN plan, what does a type value of ALL mean?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a command that shows the execution plan of a query retrieving all columns from the ksiazki table where tytul is 'Iliada'. Use EXPLAIN.

  • Code editor

    Write a command with EXPLAIN that shows the plan of a query retrieving all columns from the autorzy table where nazwisko is 'Homer'.

  • Click in order

    Click the elements in the order in which we build the command that checks a query plan:

  1. QuestionWhat EXPLAIN is for
  2. QuestionThe type column in the plan
  3. Coding taskCheck the query plan
  4. QuestionThe key column in the plan
  5. Coding taskPlan for authors
  6. Click in orderEXPLAIN for a title search

Useful articles