Reading EXPLAIN

MySQL Course · lesson 11 of 21 · 4 min read

The one tool that turns "the database is slow" into a specific, fixable problem.

Open this lesson in the learning hub

Key points

  • EXPLAIN shows the plan MySQL intends to use, without running the query.
  • type is the column that matters most: ALL is a full scan, ref and const are index lookups.
  • rows is how many rows MySQL expects to examine - a large number with a small result is the smell.
  • Using filesort means the sort could not use an index; Using temporary means a temp table was built.
  • EXPLAIN ANALYZE runs the query and reports what actually happened, not just the estimate.

Example

EXPLAIN SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.city = 'London'
GROUP BY c.name;

-- look for:
--   type: ALL          -> a scan; probably a missing index
--   rows: 850000       -> examining far more than you return
--   Extra: Using filesort   -> the ORDER BY had no usable index

Read type, then rows, then Extra - that order finds most slow queries in under a minute.

Designing a schema Connections and pooling

This is a reading copy. The full lesson — with the visual explainer, the interactive lab and a Run button for the code — lives in the MySQL Course course, and every lesson in it is listed on the MySQL Course contents page.