SQL and MySQL course Β· Module 9: Indexes and normalization
EXPLAIN - the query plan
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.
ALLmeans a full table scan (bad!), whilereforconstmeans an index is used (good!). - key - which index was used. If you see
NULLhere, 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. What is the EXPLAIN command before a SELECT query used for?
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: