Full table scan (type: ALL) in MySQL EXPLAIN

What it does

ALL in the type column of EXPLAIN means a full table scan: InnoDB reads the table from start to end, in primary key order, and every row is checked against the WHERE clause (shown as Using where). In a join, a table marked ALL is scanned in full for each combination of rows from the tables before it, unless MySQL uses a join buffer (see Using join buffer).

In EXPLAIN ANALYZE and EXPLAIN FORMAT=TREE it’s written as:

-> Table scan on orders

It’s the MySQL counterpart of PostgreSQL’s Seq Scan.

When the planner picks it

• No index fits the conditions: nothing on the filtered columns, or possible_keys is NULL.
• An index exists but the condition hides the column: a function or arithmetic on it (YEAR(created_at) = 2025, customer_id + 0 = 4242), or a LIKE starting with %. possible_keys is then NULL. A string column compared with a number (WHERE status = 0) also scans, but possible_keys still lists the index, so it looks like the next case.
• An index exists but a scan is cheaper: possible_keys lists it and key is NULL. When a condition matches a large share of the rows, reading the table sequentially beats jumping between the index and the table for each row. In our test that happened for a condition matching 70% of the rows.
• The table is small: a few pages are quicker to read whole.
• The query really wants every row: no WHERE, or an aggregate over the whole table.

Reading its numbers

• rows on an ALL row is MySQL’s estimate of the table’s row count, not of the result. On 500,000 rows it said 498,888.
• filtered is the share of rows expected to pass the WHERE clause. Without an index or a histogram on the column it’s a fixed guess (10% for =, 33.33% for a range), so treat it as rough.
• In EXPLAIN ANALYZE, rows= on Table scan is the number of rows actually read and the Filter: line above it shows how many were kept. With a LIMIT, the scan stops early, so the read count can be small even though the plan says ALL.

When it’s a problem

When the table is large, the query keeps a small fraction of the rows, and it runs often. The signs: type = ALL, a large rows, a small filtered, and in EXPLAIN ANALYZE a Filter: that keeps a tiny share of what the scan read.

Fixes, in order of what to try:

1. Add an index on the column the WHERE clause filters by, or a composite index with the equality columns first.
2. Make the condition use the bare column: created_at >= '2025-01-01' AND created_at < '2026-01-01' instead of YEAR(created_at) = 2025; compare with values of the column’s own type.
3. If the column must be wrapped in an expression, MySQL 8.0.13 and later can index the expression itself: CREATE INDEX … ON orders ((DATE(created_at))).

A scan the optimiser picked despite a usable index (possible_keys set, key NULL) is usually a correct decision: forcing the index with FORCE INDEX makes such queries slower. Check that the statistics are current (ANALYZE TABLE orders) before overriding it.

Example

On MySQL 8.4.11, an orders table with 500,000 rows:

CREATE TABLE orders (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id int NOT NULL,
  status varchar(20) NOT NULL,
  total decimal(10,2) NOT NULL,
  created_at datetime NOT NULL,
  note varchar(100) NULL,
  KEY idx_customer (customer_id),
  KEY idx_status_created (status, created_at)
);
-- 500,000 rows; 10,000 have note = 'gift wrap please'; 70% are 'paid' or 'shipped'

Orders with a gift note, with no index on note:

EXPLAIN SELECT * FROM orders WHERE note = 'gift wrap please';

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498888 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

-> Filter: (orders.note = 'gift wrap please')  (cost=50266 rows=49889) (actual time=0.301..128 rows=10000 loops=1)
    -> Table scan on orders  (cost=50266 rows=498888) (actual time=0.259..111 rows=500000 loops=1)

Every row read, 2% kept, 128 ms. With an index:

CREATE INDEX idx_note ON orders (note);

+----+-------------+--------+------------+------+---------------+----------+---------+-------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_note      | idx_note | 403     | const | 18780 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+-------+----------+-------+

-> Index lookup on orders using idx_note (note='gift wrap please')  (cost=3008 rows=18780) (actual time=0.277..10.2 rows=10000 loops=1)

10,000 rows read instead of 500,000, and 10 ms instead of 128.

The same scan with LIMIT 5 stopped after 250 rows, because the first five matches came early in the table:

-> Limit: 5 row(s)  (cost=50266 rows=5) (actual time=0.0588..0.0784 rows=5 loops=1)
    -> Filter: (orders.note = 'gift wrap please')  (cost=50266 rows=49889) (actual time=0.0579..0.0772 rows=5 loops=1)
        -> Table scan on orders  (cost=50266 rows=498888) (actual time=0.0421..0.0597 rows=250 loops=1)

If the matches were rare or at the end of the table, the same plan would read it all.

A scan chosen on purpose, despite an index on status:

EXPLAIN SELECT * FROM orders WHERE status IN ('paid', 'shipped');

+----+-------------+--------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys      | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_status_created | NULL | NULL    | NULL | 498888 |   100.00 | Using where |
+----+-------------+--------+------------+------+--------------------+------+---------+------+--------+----------+-------------+

-> Filter: (orders.`status` in ('paid','shipped'))  (cost=50266 rows=498888) (actual time=0.126..141 rows=350000 loops=1)
    -> Table scan on orders  (cost=50266 rows=498888) (actual time=0.124..84 rows=500000 loops=1)

350,000 of 500,000 rows match. possible_keys names the index; key is NULL because looking up 350,000 rows through it would cost more than one pass over the table. The optimiser was right: with FORCE INDEX (idx_status_created) the same query took 484 ms instead of 141:

-> Index range scan on orders using idx_status_created over (status = 'paid') OR (status = 'shipped'), with index condition: (orders.`status` in ('paid','shipped'))  (cost=149667 rows=332592) (actual time=3.13..484 rows=350000 loops=1)

In Inlet

Run EXPLAIN or EXPLAIN ANALYZE in Inlet’s query editor like any other statement (⌘↩). With your own Anthropic API key, Ask Claude (⌘L) can explain a plan; it sends the schema and the plan, never rows. The structure editor shows the CREATE INDEX statement before it runs and warns when a change rewrites or scans the table.