Using index condition in MySQL EXPLAIN

What it does

Using index condition means index condition pushdown (ICP) is in use. MySQL hands part of the WHERE clause down to InnoDB, which tests it against each index entry while walking the index. Only entries that pass cause a lookup of the full row.

Without it, InnoDB would fetch the full row for every index entry in the range and leave all the filtering to the server. With a secondary index, each of those fetches is a separate lookup in the table (the clustered index), so skipping them saves real work.

In EXPLAIN ANALYZE, the pushed-down condition is written on the index access line:

-> Index lookup on orders using idx_status_created (status='refunded'), with index condition: (hour(orders.created_at) = 3)

When the planner picks it

When a query uses a secondary index for a range, ref, eq_ref or ref_or_null access, and part of the WHERE clause refers only to columns stored in that index but can’t be used to narrow the search. Typical shapes, with an index on (a, b):

• WHERE a = ? AND <some test on b> that can’t narrow the search: a function (HOUR(b) = 3), a LIKE pattern that starts with %
• a range on a plus conditions on b, which InnoDB checks in the index as it walks the range

It isn’t used for the primary key in InnoDB (the full row is already there), for indexes on virtual generated columns, or for conditions with subqueries or stored functions. It’s on by default; optimizer_switch has an index_condition_pushdown flag to turn it off for testing.

Reading its numbers

Traditional EXPLAIN doesn’t say how much ICP filters. rows is still the estimate for the index range before the pushed condition, and filtered may or may not account for it.

EXPLAIN ANALYZE shows the effect: the rows= on the index line with with index condition is the number of entries that passed the condition, so the number of full rows fetched. Compare it with the rows= estimate on the same line, which counts what the range covers.

You may also see a range condition repeated as an index condition on a range scan, as in over (100 <= customer_id <= 400), with index condition: (orders.customer_id between 100 and 400). That’s MySQL re-checking the range bounds and costs next to nothing.

When it’s a problem

It isn’t: Using index condition means MySQL is avoiding row lookups. What it tells you is that the index is only partly used for the search, and InnoDB still walks every entry in the range. If that range is large and the pushed condition rejects most of it:

• Make the condition searchable: created_at >= ? AND created_at < ? instead of a function on the column, so it becomes part of the range.
• Or add an index whose leading columns match the selective conditions.

If you also see Using where, some conditions still need the full row (they use columns not in the index), and are checked after the lookup.

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; 50,000 have status 'refunded'

Refunds placed between 03:00 and 04:00, on any day:

EXPLAIN SELECT * FROM orders WHERE status = 'refunded' AND HOUR(created_at) = 3;

+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+-------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+-------+----------+-----------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_status_created | idx_status_created | 82      | const | 92148 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+-------+----------+-----------------------+

-> Index lookup on orders using idx_status_created (status='refunded'), with index condition: (hour(orders.created_at) = 3)  (cost=10345 rows=92148) (actual time=1.09..8.95 rows=2160 loops=1)

The index finds the refunds by status; created_at is in the same index entry, so InnoDB tests HOUR(created_at) = 3 before fetching rows. Only 2,160 rows were fetched, in 9 ms.

The same query with pushdown turned off for the session:

SET SESSION optimizer_switch = 'index_condition_pushdown=off';

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

-> Filter: (hour(orders.created_at) = 3)  (cost=10345 rows=92148) (actual time=0.65..58.7 rows=2160 loops=1)
    -> Index lookup on orders using idx_status_created (status='refunded')  (cost=10345 rows=92148) (actual time=0.633..56.7 rows=50000 loops=1)

Now all 50,000 refunded rows are fetched and the hour is checked afterwards (Using where): 59 ms instead of 9.

A range followed by a test on another column, with only a single-column index, shows both notes:

EXPLAIN SELECT * FROM orders WHERE customer_id BETWEEN 100 AND 400 AND note IS NOT NULL;

+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | orders | NULL       | range | idx_customer  | idx_customer | 4       | NULL | 7525 |    90.00 | Using index condition; Using where |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------+

-> Filter: (orders.note is not null)  (cost=3387 rows=6772) (actual time=0.727..10.9 rows=150 loops=1)
    -> Index range scan on orders using idx_customer over (100 <= customer_id <= 400), with index condition: (orders.customer_id between 100 and 400)  (cost=3387 rows=7525) (actual time=0.67..10.6 rows=7525 loops=1)

note isn’t in the index, so it can only be checked after each of the 7,525 rows is fetched, and only 150 pass. The filtered estimate of 90% was far off; the real figure is 2%. With an index on (customer_id, note), InnoDB checks note in the index too, and only the 150 matching rows are fetched:

-> Index range scan on orders using idx_customer_note over (100 <= customer_id <= 400 AND NULL < note), with index condition: ((orders.customer_id between 100 and 400) and (orders.note is not null))  (cost=3375 rows=7500) (actual time=1.46..1.47 rows=150 loops=1)

1.5 ms instead of 10.9.

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.