TopolearnEXPLAIN is how you confirm an index is used and find full scans, filesorts and temporary tables.
EXPLAIN SELECT ... shows the optimizer's plan without running the query. In the traditional table format, each row is a table access:
- type, from best to worst:
const/eq_ref(one row by unique key),ref(non-unique index lookup),range(index range),index(full index scan), `ALL` (full table scan). - key: the index chosen;
possible_keyslists candidates. - rows and filtered: estimated rows examined and the percentage kept by the
WHEREclause. - Extra:
Using index(covering, good),Using where,Using filesort(an extra sort step),Using temporary(a temporary table, often forGROUP BY/DISTINCT).
EXPLAIN FORMAT=TREE shows the plan as an iterator tree, and `EXPLAIN ANALYZE` (8.0.18+) actually runs the query and annotates each node with estimated cost and rows next to actual time, rows and loops. Large gaps between estimated and actual rows point to stale statistics or a missing histogram.
Workflow: find slow statements in the slow query log (slow_query_log = ON, long_query_time = 0.5) or sys.statements_with_runtimes_in_95th_percentile, then EXPLAIN ANALYZE them, look for ALL, filesorts and big row counts, and add or fix indexes.
EXPLAIN ANALYZE executes the statement, so be careful with expensive queries on production.
Example
CREATE TABLE customer (id INT PRIMARY KEY, country CHAR(2), KEY (country));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2));
EXPLAIN SELECT c.id, SUM(o.total)
FROM customer c JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'AM' GROUP BY c.id;
-- table c: type=ref key=country
-- table o: type=ALL key=NULL Extra: Using where; Using join buffer (hash join)
CREATE INDEX idx_customer ON orders (customer_id);
EXPLAIN ANALYZE SELECT c.id, SUM(o.total)
FROM customer c JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'AM' GROUP BY c.id\G
-- (exact shape depends on the data), for example:
-- -> Group aggregate: sum(o.total) (actual time=... rows=... loops=1)
-- -> Nested loop inner join (cost=... rows=...)
-- -> Covering index lookup on c using country (country='AM')
-- -> Index lookup on o using idx_customer (customer_id=c.id)Remember
- type=ALL means a full table scan; ref/eq_ref/const are index lookups
- Using filesort and Using temporary flag extra work
- EXPLAIN ANALYZE runs the query and shows actual rows and time
Watch out for
- Reading only the key column and missing that rows estimates millions of rows examined.