Using where in MySQL EXPLAIN

What it does

Using where means the server tests a condition against each row it gets from the table and discards the rows that fail, before passing the rest to the next table in the join or to the client. The condition is whatever part of the WHERE clause (or a join’s ON) the index access couldn’t already guarantee.

In EXPLAIN ANALYZE it appears as a Filter: line above the access that produced the rows:

-> Filter: (orders.total > 400.00)
    -> Index lookup on orders using idx_customer (customer_id=4242)

When the planner picks it

Almost every query with a WHERE clause shows it somewhere. MySQL adds a filter when:

• No index is used for the table (type is ALL): every condition is checked row by row.
• An index finds some of the rows, and the rest of the conditions need columns the index doesn’t have, or can’t be expressed as an index search (a function on a column, OR across columns, a LIKE starting with %).
• A join condition has to be checked on rows from the joined table.

It’s absent when the index access alone guarantees the result: WHERE id = 1 by primary key shows no Using where.

Reading its numbers

Two columns of traditional EXPLAIN matter here:

• rows: how many rows MySQL expects to read from this table (per row of the previous table in a join).
• filtered: the percentage of those it expects to keep after the filter. rows × filtered ÷ 100 is the expected output.

A big rows with a small filtered is the pattern to look for: lots of reading, little kept. EXPLAIN ANALYZE gives the real numbers, the rows= under Filter: (read) and on Filter: (kept).

filtered is an estimate, and for columns without an index or a histogram it’s often a fixed guess: 10% for =, 33.33% for a range. A histogram gives the optimiser real figures:

ANALYZE TABLE orders UPDATE HISTOGRAM ON note;

When it’s a problem

Using where on a ref, eq_ref or range row that reads a handful of rows is fine. Look closer when:

• It sits next to type: ALL (a full table scan) or type: index (a full index scan) and the query returns few rows. MySQL reads the whole table to find them. The fix is usually an index on the filtered column.
• EXPLAIN ANALYZE shows the filter keeping a small fraction of a large input, even with an index in use. A composite index that includes the filtered column moves the test into the index (see Using index condition) or makes it part of the search.
• The condition wraps an indexed column in a function or arithmetic, such as WHERE customer_id + 0 = 4242 or WHERE DATE(created_at) = '2025-01-01'. The index can’t be searched; rewrite it as a comparison on the bare column.

The MySQL manual puts it the other way round: if the Extra column does not say Using where and type is ALL or index, and you didn’t mean to read every row, the query is probably wrong.

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 over 20,000 customers; 10,000 rows have note = 'gift wrap please'

An index finds a customer’s orders; the total is checked afterwards:

EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 AND total > 400;

+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer  | idx_customer | 4       | const |   25 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+

-> Filter: (orders.total > 400.00)  (cost=7.08 rows=8.33) (actual time=0.0801..0.112 rows=5 loops=1)
    -> Index lookup on orders using idx_customer (customer_id=4242)  (cost=7.08 rows=25) (actual time=0.0473..0.0786 rows=25 loops=1)

25 rows read, 5 kept, 0.1 ms. Nothing to fix.

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)

500,000 rows read to keep 10,000, in 128 ms. filtered says 10.00, the default guess for =; the real share is 2%. After ANALYZE TABLE orders UPDATE HISTOGRAM ON note, the same EXPLAIN showed filtered = 2.01. The estimate improved; the plan didn’t, because a histogram can’t replace an index. With CREATE INDEX idx_note ON orders (note), the access became ref and the query read only the 10,000 matching rows, in 10 ms (see full table scan).

An index the condition can’t use:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id + 0 = 4242;

-> Filter: ((orders.customer_id + 0) = 4242)  (cost=50266 rows=498888) (actual time=3.41..104 rows=25 loops=1)
    -> Table scan on orders  (cost=50266 rows=498888) (actual time=0.126..81.5 rows=500000 loops=1)

idx_customer exists, but customer_id + 0 isn’t customer_id: 500,000 rows read for 25, where WHERE customer_id = 4242 reads 25.

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.