Why is MySQL not using my index?

Quick answer: Usually the optimizer is right. Four causes cover most cases: a function wrapped around the column, a type or collation mismatch that makes MySQL convert the column instead of the literal, a composite index entered without its leading column, and a predicate that matches so much of the table that a scan is cheaper. Fix the predicate or the index. Reach for FORCE INDEX to test a hypothesis, never to ship one.

The index is there. EXPLAIN says type: ALL. You may suspect that the optimizer has made a mistake.

Usually, it has not. Either the index cannot be used for that predicate, or using it would be slower. Four causes cover most cases.

Set up a table to demonstrate against:

CREATE DATABASE index_demo;
USE index_demo;

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL,
  account_no VARCHAR(20)  NOT NULL,
  country    CHAR(2)      NOT NULL,
  created_at DATETIME     NOT NULL,
  INDEX idx_created (created_at),
  INDEX idx_account (account_no),
  INDEX idx_country_created (country, created_at)
);

INSERT INTO users (email, account_no, country, created_at)
SELECT
  CONCAT('user', a.ordinal_position, b.ordinal_position, '@example.com'),
  LPAD(FLOOR(RAND() * 999999), 8, '0'),
  ELT(FLOOR(1 + RAND() * 3), 'GB', 'US', 'DE'),
  NOW() - INTERVAL FLOOR(RAND() * 500) DAY
FROM information_schema.columns a, information_schema.columns b
LIMIT 20000;

ANALYZE TABLE users;

1. A function on the column

This cannot use idx_created, even when the index is otherwise suitable:

EXPLAIN SELECT * FROM users WHERE DATE(created_at) = '2026-01-01';

Measured on MySQL 8.4.11, trimmed to the columns that matter:

type: ALL   possible_keys: NULL   key: NULL   rows: 20318   Extra: Using where

The index stores created_at. It does not store DATE(created_at). MySQL has to compute the function for every row before it can compare, and computing something for every row is a full scan.

Note possible_keys: NULL. The optimizer did not weigh idx_created and reject it. The index was never a candidate. That distinction separates this cause from the three below.

Rewrite it as a range over the raw column:

EXPLAIN SELECT * FROM users
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

type: range   possible_keys: idx_created   key: idx_created   rows: 32
Extra: Using index condition

Same rows, 32 of them, and now type: range. This shape, with the column left bare on one side of the comparison, is what people mean by a sargable predicate.

2. A type mismatch

account_no is a VARCHAR. Compare it to a number and the index goes away:

EXPLAIN SELECT * FROM users WHERE account_no = 12345678;

type: ALL   possible_keys: idx_account   key: NULL   rows: 20318   filtered: 10.00
Extra: Using where

MySQL converts the column to a number, not the literal to a string. That is implicitly the same as wrapping every row in a function, with the same result and none of the visibility.

Look at possible_keys again. Here it names idx_account while key is NULL, where the function case had possible_keys: NULL. The index was a candidate this time and still went unused. This distinction is useful:

possible_keys	key	what it means
NULL	NULL	the index cannot apply to this predicate at all
names an index	NULL	the index applies and the optimizer judged it more expensive

The first is a query you must rewrite. The second is a cost decision you can argue with, usually by fixing statistics. Treating them as one problem is why people reach for FORCE INDEX on queries that need rewriting instead.

Quote the literal:

EXPLAIN SELECT * FROM users WHERE account_no = '12345678';

type: ref   possible_keys: idx_account   key: idx_account   key_len: 82
ref: const   rows: 1

The same thing happens across a join when two columns have different collations, and it is harder to spot because neither side looks wrong on its own.

3. Missing the leading column

idx_country_created is (country, created_at). A composite index can only be entered from the left. The one exception is skip scan, which among other conditions needs a query that references only the index’s own columns, and SELECT * does not.

To see this index on its own, the other index on created_at has to be taken off the table, or it answers the query and hides the point:

-- no leading column, and a range narrow enough that an index would obviously win
EXPLAIN SELECT * FROM users IGNORE INDEX (idx_created)
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

type: ALL   possible_keys: NULL   key: NULL   rows: 20291

Thirty-two rows out of twenty thousand match, an index would be cheaper, and possible_keys is still NULL. The optimizer is not weighing the index and declining; there is nothing to weigh.

Add the leading column and the same index on the same query becomes a range scan:

EXPLAIN SELECT * FROM users IGNORE INDEX (idx_created)
WHERE country = 'GB' AND created_at >= '2026-01-01' AND created_at < '2026-01-02';

type: range   possible_keys: idx_country_created   key: idx_country_created
key_len: 13   rows: 9   Extra: Using index condition

The index is sorted by country first. Without a country value you have no idea where to start reading, so there is nothing to seek to. Column order in a composite index is a design decision, not a formality.

One trap is worth naming because it can hide this cause. On the real table, with idx_created present, that first query comes back type: ALL with possible_keys: idx_created and key: NULL. The full scan is real, but the reason is now cause 4 rather than cause 3, and the composite index never entered the decision at all. A single EXPLAIN on a table with several indexes can show one cause hiding another.

4. The predicate matches too much

With three countries in 20,000 rows, this matches roughly a third of the table:

EXPLAIN SELECT * FROM users WHERE country = 'GB';

Measured on 8.4.11 it comes back using the index:

type: ref   possible_keys: idx_country_created   key: idx_country_created
key_len: 8   ref: const   rows: 10159   Extra: Using index condition

The optimizer is willing to use an index for a third of a table, and on this data it is right to.

Read that rows: 10159 carefully, though, because it is an estimate and the real answer was 6,571. The optimizer was more than fifty percent over on a single-column equality against freshly analyzed statistics. rows tells you what the decision was made from, not what the query will do. The threshold people quote, that a scan wins past 20 to 30 percent, is a rule of thumb rather than a rule: it moves with row width, index width, whether the index covers the query, and how much of the table is already in the buffer pool.

When you do see type: ALL on a low-selectivity predicate, that is usually the optimizer being right, not broken. Using a secondary index means walking the index and then fetching each row from the table in index order, which is effectively random order on disk. At some fraction of the table, reading it straight through beats jumping around. Where that fraction falls is the optimizer’s job to estimate, and it has statistics you do not.

Check what the optimizer believes before you argue with it:

SELECT country, COUNT(*) FROM users GROUP BY country;
ANALYZE TABLE users;

If the real distribution and the estimate disagree, stale statistics were the problem and ANALYZE TABLE is the fix.

Testing a hypothesis without shipping it

To confirm the index would help, force it once and compare against the same query unforced:

EXPLAIN ANALYZE SELECT * FROM users FORCE INDEX (idx_created)
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

EXPLAIN ANALYZE SELECT * FROM users
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

Measured on 8.4.11, the two came back as the same plan:

forced:    Index range scan on users using idx_created  (cost=0.71 rows=1)
           (actual time=0.0514..0.207 rows=32 loops=1)

unforced:  Index range scan on users using idx_created  (cost=14.7 rows=32)
           (actual time=0.056..0.157 rows=32 loops=1)

The hint changed nothing about what ran, because the optimizer had already chosen that index. What it did change is the estimate: cost fell from 14.7 to 0.71 and rows from 32 to 1, because forcing an index tells the optimizer to stop costing the alternatives rather than making the index cheaper. Compare the actual figures, never the cost. A hint always makes its own plan look cheap.

Use FORCE INDEX to learn, not to ship. It hard-codes a decision that is correct for today’s data and stays hard-coded after the data changes. A hint that was right in March is a bug in September, and the failure may go unnoticed.

Clean up

DROP DATABASE index_demo;

How DBGorilla helps

DBGorilla can help

DBGorilla captures your schema with mysqldump, so your AI coding agent can see the index definitions and column types that are really on the table rather than the ones the migration file intended. Two of the four causes above (the type mismatch and the missing leading column) are answerable from that alone. The fourth, a predicate that matches too much, is answerable from the digest statistics DBGorilla already holds. DBGorilla also runs EXPLAIN for you, so your agent can read the plan the optimizer actually chose.

Start free

FAQ

• Should I use FORCE INDEX to make it behave?

  Almost never, and never as a first move. FORCE INDEX freezes a decision that was correct against today's data distribution and will still be frozen when the data changes. Use it to test a hypothesis, then fix the underlying cause.

• Why does wrapping a column in a function stop the index working?

  A B-tree index stores the column's values, not the function's output. DATE(created_at) = '2026-01-01' has to compute DATE() for every row before it can compare, so there is nothing to look up. Rewrite it as a range over the raw column instead.

• Can a type mismatch really disable an index?

  Yes, and it is the most common invisible cause. Comparing a VARCHAR column to a number makes MySQL convert the column, not the literal, which is the same problem as wrapping it in a function. Quote the literal to match the column type.

• Why does MySQL scan when my WHERE matches half the table?

  Because at some point the scan is genuinely cheaper. Using a secondary index means reading the index and then fetching each matching row from the table, in index order rather than disk order. Where the crossover falls depends on row width, whether the index covers the query, and what is already in the buffer pool, so treat any single percentage you read as a rule of thumb. The optimizer estimates it from statistics you do not have in front of you.

Related

• How to add an index in MySQL without downtime
• How to find slow queries in MySQL
• How to read a MySQL EXPLAIN plan
• Product: DBGorilla docs