MySQL Index Not Used: Why Queries Ignore Indexes

“I added an index, but the query is still slow, and EXPLAIN shows key: NULL.” This is one of the most common MySQL questions. The cause is almost always one of the following.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(60) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    city VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    signup_date DATE NOT NULL,
    KEY idx_email (email),
    KEY idx_phone (phone),
    KEY idx_city_score (city, score),
    KEY idx_signup (signup_date)
);
INSERT INTO customers (email, phone, city, score, signup_date)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 30000)
SELECT CONCAT('user', i, '@example.com'), CONCAT('98', LPAD(i, 8, '0')), CONCAT('City', MOD(i, 200)), MOD(i * 7, 1000), '2025-01-01' + INTERVAL MOD(i, 500) DAY FROM n;
ANALYZE TABLE customers;

1. A function on the indexed column

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE UPPER(email) = 'USER42@EXAMPLE.COM';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email = 'user42@example.com';

Output

OUTPUT

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | index | NULL          | idx_email | 242     | NULL | 30169 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
1 row in set

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_email     | idx_email | 242     | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set

The first query wraps the column in a function: no seek. The second uses idx_email. (The default collation is case-insensitive, so UPPER was never needed.) See Sargable Conditions.

2. A type mismatch

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE phone = 9800000042;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE phone = '9800000042';

Output

OUTPUT

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | index | idx_phone     | idx_phone | 62      | NULL | 30169 |    10.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
1 row in set

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_phone     | idx_phone | 62      | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set

phone is a string. Comparing it with a number forces a conversion of every row. Quote the value (Type Conversion).

3. A leading wildcard

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email LIKE '%@example.com';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email LIKE 'user42%';

Output

OUTPUT

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | index | NULL          | idx_email | 242     | NULL | 30169 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
1 row in set

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_email     | idx_email | 242     | NULL |  111 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set

An index is sorted from the start of the value. A pattern that starts with % has no starting point.

4. Not the leftmost column of a composite index

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE score = 500;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE city = 'City7' AND score = 500;

Output

OUTPUT

+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_city_score | idx_city_score | 86      | NULL | 6033 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+
1 row in set

+----+-------------+-----------+------------+------+----------------+----------------+---------+-------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_city_score | idx_city_score | 86      | const,const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------------+------+----------+-------------+
1 row in set

idx_city_score starts with city. A query on score alone cannot use it to seek (Composite Indexes).

5. A range before an equality in the index

For (city, score) an equality on city and a range on score works well. If the index were (score, city), the range on score would stop MySQL from using city to narrow further.

6. Low selectivity: the optimizer chooses a scan

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE signup_date >= '2025-01-01';

Output

OUTPUT

+----+-------------+-----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_signup    | idx_signup | 3       | NULL | 15084 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
1 row in set

The condition matches all rows. Reading the whole table is cheaper than visiting every index entry and then every row. This is a correct decision, not a bug.

7. OR across different columns

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email = 'user42@example.com' OR city = 'City7';

Output

OUTPUT

+----+-------------+-----------+------------+-------------+--------------------------+--------------------------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table     | partitions | type        | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-----------+------------+-------------+--------------------------+--------------------------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | customers | NULL       | index_merge | idx_email,idx_city_score | idx_email,idx_city_score | 242,82  | NULL |  151 |   100.00 | Using sort_union(idx_email,idx_city_score); Using where |
+----+-------------+-----------+------------+-------------+--------------------------+--------------------------+---------+------+------+----------+---------------------------------------------------------+
1 row in set

MySQL can use an index merge union if both sides have an index (as here). If one side has none, the whole query scans. Rewriting with UNION is sometimes better.

8. Different collation or character set in a join

Joining utf8mb4_0900_ai_ci text to utf8mb4_unicode_ci text (or latin1 to utf8mb4) forces a conversion on one side, which blocks the index on that side. Keep joined columns the same type, character set and collation (Character Sets and Collations).

9. Stale statistics

After a big load or a big delete, the optimizer’s estimates can be wrong, and it picks a bad plan:

query.sql

SQL

ANALYZE TABLE customers;

Output

OUTPUT

+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| learn.customers | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set

10. The query asks for too much

SELECT * forces a table lookup for each index hit. If the lookups would be many, MySQL may prefer a scan. A narrower select list (and a covering index) changes the plan (Covering Indexes).

11. A very small table

On a table with a few hundred rows, a scan is as fast, and MySQL knows it.

12. An invisible index

An index made INVISIBLE is ignored by the optimizer (Invisible Indexes).

How to investigate

Run EXPLAIN on the real query, with the real values.
Read possible_keys (which indexes could apply), key (which was chosen), type, rows and Extra.
If possible_keys is NULL: no index fits the condition. Look for causes 1 to 4.
If possible_keys lists the index but key is NULL: the optimizer judged a scan to be cheaper. Check selectivity, statistics and the select list.
Compare with EXPLAIN ANALYZE to see real row counts against estimates (EXPLAIN ANALYZE).

Forcing an index (last resort)

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers FORCE INDEX (idx_signup) WHERE signup_date >= '2025-06-01';

Output

OUTPUT

+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_signup    | idx_signup | 3       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in set

USE INDEX, FORCE INDEX and IGNORE INDEX hints override the optimizer. They are brittle: data changes and the forced plan may become the slow one. Fix the cause (statistics, query shape, index design) before you reach for a hint (Optimizer Hints).

lightbulb

Work from the plan

Do not guess why an index is ignored. EXPLAIN tells you possible_keys, key and rows. One minute with the plan is worth an hour of theory.