Skip to content

Why is MySQL not using my index?

Last updated

View as Markdown

Quick answer: Usually the optimizer is right. Four causes cover nearly every case: 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 genuinely 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. The instinct is that the optimizer has made a mistake.

Usually it has not. Either the index genuinely cannot be used for that predicate, or using it would genuinely be slower. Four causes cover nearly every real case.

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

Section titled “1. A function on the column”

This cannot use idx_created, no matter how good the index is:

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

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.

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';

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

2. A type mismatch

Section titled “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;

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. Quote the literal:

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

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

3. Missing the leading column

Section titled “3. Missing the leading column”

idx_country_created is (country, created_at). A composite index can only be entered from the left:

-- uses the index
EXPLAIN SELECT * FROM users WHERE country = 'GB';


-- uses the index
EXPLAIN SELECT * FROM users WHERE country = 'GB' AND created_at > '2026-01-01';


-- cannot use it for lookup: no leading column
EXPLAIN SELECT * FROM users WHERE created_at > '2026-01-01' AND country IS NOT NULL;

Think of the index as 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.

4. The predicate matches too much

Section titled “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';

On MySQL 8.4 this actually comes back type: ref, using idx_country_created with rows around 10,000. The optimizer is willing to use an index for a third of a table, and on this data it is right to. 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.

Which is the real point. 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

Section titled “Testing a hypothesis without shipping it”

To confirm the index would help, force it once and compare:

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

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 it will not announce itself.

Clean up

Section titled “Clean up”
DROP DATABASE index_demo;
Section titled “Related”