Skip to main content
MySQL · lesson 1 of 8

Beginner

1

Intermediate

6

Advanced

1

Also part of MySQL

1
Open the MySQL page →
MySQL beginner Lesson 1 of 8

MySQL EXPLAIN: Reading a Full Table Scan Before You Fix It

A filtered query against 5,000 rows with no index scans every single row — EXPLAIN and EXPLAIN ANALYZE show exactly that, in real numbers, before an index changes the plan entirely.

By SkillByExample · Published

mysql explain query-optimization getting-started

A query that “just filters one column” can mean reading every row in the table — EXPLAIN is how you find out which one is actually happening.

A query, and what it costs

SELECT COUNT(*) FROM books WHERE genre = 'SciFi';
COUNT(*)
834

834 of the 5,000 books are tagged SciFi. That number matters for what comes next — it’s the real answer, so we’ll know exactly what a correct query plan should report.

The bug: a full table scan on every genre lookup

EXPLAIN SELECT * FROM books WHERE genre = 'SciFi';
id  select_type  table  partitions  type  possible_keys  key   key_len  ref   rows  filtered  Extra
1   SIMPLE       books  NULL        ALL   NULL           NULL  NULL     NULL  4980  10.00     Using where

type: ALL is a full table scan — MySQL has no index on genre, so the only way to find matching rows is to read all of them and filter afterward. rows: 4980 is the planner’s estimate of how many rows it’ll have to examine to do that — essentially the whole table, to find 834 matches.

EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'SciFi';
-> Filter: (books.genre = 'SciFi')  (cost=503 rows=498) (actual time=0.0475..1.56 rows=834 loops=1)
    -> Table scan on books  (cost=503 rows=4980) (actual time=0.0377..1.25 rows=5000 loops=1)

EXPLAIN ANALYZE actually runs the query — actual rows=5000 on the table scan confirms every row was really read, and actual time=...1.25 (milliseconds) is genuinely measured, not estimated. The outer Filter step narrows those 5,000 down to the real 834 matches.

The fix: an index on the filtered column

CREATE INDEX idx_books_genre ON books(genre);
EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'SciFi';
-> Index lookup on books using idx_books_genre (genre='SciFi')  (cost=99.2 rows=834) (actual time=0.0845..0.733 rows=834 loops=1)

The plan changed shape entirely — Index lookup instead of Table scan, and actual rows=834 this time, not 5000. MySQL went straight to the matching rows using the index instead of reading and discarding the other 4,166. Measured time dropped from 1.56ms to 0.733ms even at this modest table size; the gap widens sharply as tables grow into the millions of rows, which is the whole reason this distinction matters in production.

Practice

1. Run EXPLAIN on a query filtering by a column that already has an index (the primary key). 
EXPLAIN SELECT * FROM books WHERE id = 2500;
id  select_type  table  partitions  type   possible_keys  key      key_len  ref     rows  filtered  Extra
1   SIMPLE       books  NULL        const  PRIMARY        PRIMARY  4        const   1     100.00    NULL

type: const — even faster than an index lookup, because MySQL knows a primary key match returns at most one row and can treat it as a constant. rows: 1, filtered: 100.00, no Using where needed since there’s nothing left to filter after finding the single row.

2. Check whether MySQL uses the new genre index for a filter that matches most of the table. 
EXPLAIN SELECT * FROM books WHERE genre != 'SciFi';
id  select_type  table  partitions  type  possible_keys    key   key_len  ref   rows  filtered  Extra
1   SIMPLE       books  NULL        ALL   idx_books_genre  NULL  NULL     NULL  4980  83.67     Using where

possible_keys lists idx_books_genre as available, but key is NULL — the optimizer chose not to use it. Excluding one genre out of six still matches roughly 4,166 of 5,000 rows, and MySQL correctly judged a full scan cheaper than an index lookup that would still need to touch most of the table anyway.

3. Compare EXPLAIN's row estimate against the real count for a two-condition filter. 
SELECT COUNT(*) FROM books WHERE genre = 'SciFi' AND published_year > 2010;
EXPLAIN SELECT * FROM books WHERE genre = 'SciFi' AND published_year > 2010;
COUNT(*)
278
id  select_type  table  partitions  type  possible_keys    key              key_len  ref    rows  filtered  Extra
1   SIMPLE       books  NULL        ref   idx_books_genre  idx_books_genre  203      const  834   33.33     Using where

The index narrows to the 834 SciFi rows first (rows: 834), then filters those further by published_year in memory (filtered: 33.33 — close to the real 278/834 ≈ 33.3% ratio) — only one column is indexed, so the second condition can’t use an index lookup of its own yet. The next lesson covers exactly this: indexing more than one column together.

Next: indexing strategy — composite indexes, and why column order in the index matters.

Frequently Asked Questions

What's the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the query planner's estimated plan — what it thinks it will do, based on table statistics, without actually running the query. EXPLAIN ANALYZE actually executes the query and reports real measured numbers (actual time, actual rows) alongside the plan, which is what you want when the estimate and reality might differ.
What does type: ALL mean in an EXPLAIN plan?
ALL means a full table scan — MySQL is reading every single row in the table to find the ones that match, because there's no index it can use to jump directly to the relevant rows. It's the slowest access type EXPLAIN reports, and the first thing worth checking when a query feels slower than it should.
Why did rows go from 5000 to 834 after adding the index?
5000 was the full table scan's estimate — read every row and filter afterward. 834 is the actual number of rows that match genre = 'SciFi' (confirmed by the earlier COUNT(*) query) — the index lookup goes straight to just those rows instead of reading (and discarding) the other 4,166.
Does an index always make a query faster?
For selective filters — where the matching rows are a small fraction of the table — yes, dramatically. For a filter that matches most of the table, MySQL's optimizer may decide a full table scan is actually cheaper than the overhead of an index lookup plus scattered row reads, and will ignore the index entirely. The plan (via EXPLAIN), not the mere existence of an index, is what tells you which is happening.
Why does the EXPLAIN ANALYZE output show nested steps?
Each line is one operation in the execution plan, indented to show what feeds into what — a Filter step reading from a Table scan (or Index lookup) beneath it. Reading from the innermost (most indented) line outward shows the actual order operations happen in, which matters once plans get more complex with joins.