# Why is MySQL not using my index?

> **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:

```sql
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`, no matter how good the index is:

```sql
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:

```sql
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

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

```sql
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:

```sql
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

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

```sql
-- 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

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

```sql
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:

```sql
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:

```sql
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

```sql
DROP DATABASE index_demo;
```

## Related

- [How to add an index in MySQL without downtime](https://www.dbgorilla.com/learn/mysql/how-to-add-an-index-in-mysql-without-downtime/)
- [How to find slow queries in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-find-slow-queries-in-mysql/)
- [How to read a MySQL EXPLAIN plan](https://www.dbgorilla.com/learn/mysql/how-to-read-a-mysql-explain-plan/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)