Understanding MySQL EXPLAIN Extra Column: Using Index, Index Condition Push‑Down, and Using Where

Extra

Extra is an important column in MySQL EXPLAIN output that shows detailed information about the query execution process.

Using index

When an index cover is used, the Extra column shows "Using index". This happens when all selected fields are contained in the index and the WHERE clause fields are also covered by the index.

mysql> explain select first_name,last_name,birth_date from employees where first_name='Mayuri' and last_name like 'Alpay' and birth_date > '1968-01-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: range
possible_keys: idx_a
          key: idx_a
      key_len: 127
          ref: NULL
         rows: 1
      filtered: 100.00
        Extra: Using where; Using index

Using index condition

When index condition push‑down is applied, the Extra column displays "Using index condition". This allows MySQL to filter rows at the storage engine level using parts of a secondary index that cannot be fully used in the index scan.

The pushed‑down condition must involve fields that belong to the secondary index.

It greatly reduces random I/O during back‑table lookups.

It also reduces the amount of data transferred from the storage engine to the MySQL server.

Remaining conditions that cannot use the index are still evaluated in the MySQL server.

Example 1

Composite index idx_a(first_name, last_name, birth_date) exists.

mysql> explain select * from employees where first_name='Mayuri' and last_name like '%Alpay' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
      filtered: 11.11
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

The WHERE clause uses first_name='Mayuri' (indexable) and last_name like '%Alpay' (leftmost character not fixed, not indexable). MySQL pushes the latter condition to InnoDB for filtering after the first condition is applied.

Example 2

Same composite index idx_a. Query:

select * from employees where first_name > 'Mayuri' and last_name = 'Alpay';

MySQL uses the index to locate rows where first_name > 'Mayuri', then pushes the last_name = 'Alpay' condition to the storage engine.

Example 3

Same index, query:

mysql> explain select * from employees where first_name='Mayuri' and last_name > 'Alpay' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: range
possible_keys: idx_a
          key: idx_a
      key_len: 124
          ref: NULL
         rows: 226
      filtered: 100.00
        Extra: Using index condition

Although "Using index condition" appears, index condition push‑down is not actually needed because the first index column is fixed and the second column remains ordered; this is considered a MySQL bug, as noted by a comment from Ding Qi.

Example 4

If the query can be satisfied by an index cover (no back‑table needed), the Extra column shows "Using where; Using index" even when index condition push‑down is possible.

mysql> explain select first_name,last_name,birth_date from employees where first_name > 'Mayuri' and last_name > 'Alpay' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: range
possible_keys: idx_a
          key: idx_a
      key_len: 124
          ref: NULL
         rows: 226
      filtered: 100.00
        Extra: Using where; Using index

Using where

The "Using where" flag indicates that MySQL server still needs to apply some WHERE conditions after the storage engine returns rows. This can appear together with "Using index" or "Using index condition".

Full table scan: MySQL server filters rows.

mysql> explain select emp_no,first_name,last_name from employees where hire_date = '1959-12-06' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299454
      filtered: 10.00
        Extra: Using where

Index access with additional non‑indexable conditions.

mysql> explain select emp_no,first_name,last_name from employees where first_name='Mayuri' and hire_date='1959-12-06' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
      filtered: 10.00
        Extra: Using where

Index access with index cover but with a condition that cannot use the index (e.g., LIKE '%abc').

mysql> explain select first_name,last_name,birth_date from employees where first_name='Mayuri' and last_name like '%Alpay' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
      filtered: 11.11
        Extra: Using where; Using index

Index access with index condition push‑down and additional server‑side conditions.

mysql> explain select * from employees where first_name='Mayuri' and last_name like '%Alpay' and hire_date>'1969-01-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
          type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
      filtered: 3.70
        Extra: Using index condition; Using where

Summary

In short, any query that uses an index filter will trigger index condition push‑down, but the presence of "Using index condition" does not guarantee that push‑down actually occurred. For a deeper understanding of index filters, see the referenced article on SQL WHERE conditions.

https://www.jianshu.com/p/89ec04641e72