Fix: MySQL Index Not Being Used — Query Optimizer Skipping Indexes

The Problem

A query is slow even though an index exists on the column being filtered:

-- index exists on users.email
CREATE INDEX idx_users_email ON users(email);

-- But EXPLAIN shows a full table scan
EXPLAIN SELECT * FROM users WHERE email = 123;

-- Output:
-- type: ALL  ← Full table scan — index not used
-- possible_keys: idx_users_email
-- key: NULL  ← Optimizer chose not to use the index
-- rows: 500000

Or a composite index exists but only part of it is used:

CREATE INDEX idx_orders ON orders(user_id, status, created_at);

-- Only user_id part of the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';
-- key_len shows only user_id is being used, not created_at

Or EXPLAIN shows possible_keys lists the index but key is NULL:

id: 1
select_type: SIMPLE
table: products
type: ALL
possible_keys: idx_price
key: NULL
rows: 1000000
Extra: Using where

Why This Happens

MySQL’s query optimizer decides whether to use an index based on cost estimation. It skips an index when:

• Type mismatch (implicit conversion) — comparing a VARCHAR column to an integer causes an implicit type cast that prevents index use. The optimizer must evaluate the expression for every row.
• Function applied to the indexed column — WHERE DATE(created_at) = '2026-01-01' applies a function to created_at, making the index unusable. The index stores raw values, not computed results.
• Leading column of composite index skipped — MySQL can only use a composite index from the leftmost column. If you filter on status but not user_id, and the index is (user_id, status), the index can’t be used.
• Low cardinality — if a column has very few distinct values (e.g., a status column with 3 possible values in a table of 1M rows), the optimizer may determine a full table scan is faster than the index + row lookups.
• LIKE with leading wildcard — WHERE name LIKE '%alice%' can’t use a B-tree index because the value could start with anything. Only LIKE 'alice%' (prefix match) uses an index.
• Small table — for tables with a few hundred rows, a full scan is often faster than an index lookup. The optimizer correctly skips the index.
• Outdated statistics — if table statistics are stale (after a large batch insert or delete), the optimizer may miscalculate the cost and choose the wrong plan.

Fix 1: Run EXPLAIN to Diagnose

Always start with EXPLAIN (or EXPLAIN ANALYZE in MySQL 8.0+) to see the actual execution plan:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

Key columns to check:

EXPLAIN ANALYZE in MySQL 8.0+ shows actual execution stats:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Output includes:
-- -> Index lookup on orders using idx_user_status (user_id=42, status='pending')
--    (cost=1.10 rows=3) (actual time=0.035..0.037 rows=3 loops=1)

actual time vs estimated cost reveals optimizer estimation errors.

Fix 2: Fix Type Mismatches (Implicit Conversion)

An implicit type conversion disables index use. The column type and the comparison value must match:

-- Table: users.id is INT, email is VARCHAR(255)
CREATE INDEX idx_users_email ON users(email);

-- WRONG — comparing VARCHAR column to an integer
-- MySQL converts every email value to a number before comparing
SELECT * FROM users WHERE email = 12345;
-- EXPLAIN: type=ALL, key=NULL

-- CORRECT — use the matching type
SELECT * FROM users WHERE email = '12345';
-- or if you mean the ID:
SELECT * FROM users WHERE id = 12345;

Common type mismatch patterns:

-- Integer column compared to string → implicit cast
WHERE int_column = '42'       -- Works but may disable index

-- String column compared to integer → disables index
WHERE varchar_column = 42     -- Never use index

-- Date column compared to string (varies by MySQL version)
WHERE date_column = '2026-03-21'   -- Usually fine in MySQL
WHERE date_column = 20260321       -- Integer comparison — may disable index

-- Check character set mismatch
-- column: utf8mb4, comparison value: latin1 → no index

Diagnose with EXPLAIN:

-- Check warnings after EXPLAIN for implicit conversion notes
EXPLAIN SELECT * FROM users WHERE email = 12345;
SHOW WARNINGS;
-- Note: Cannot use index 'idx_users_email' due to type or collation mismatch

Fix 3: Avoid Functions on Indexed Columns in WHERE

Wrapping an indexed column in a function prevents index use — rewrite the query to apply the function to the comparison value instead:

-- WRONG — function on the indexed column
-- MySQL can't use the index because it stores raw created_at values, not DATE(created_at)
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-21';
-- EXPLAIN: type=ALL

-- CORRECT — range on the raw column (index usable)
SELECT * FROM orders
WHERE created_at >= '2026-03-21 00:00:00'
  AND created_at <  '2026-03-22 00:00:00';
-- EXPLAIN: type=range, key=idx_created_at ✓

-- Or for the current day
WHERE created_at >= CURDATE()
  AND created_at < CURDATE() + INTERVAL 1 DAY

Other function rewrites:

-- WRONG — YEAR() function disables index
WHERE YEAR(created_at) = 2026

-- CORRECT — range on the column
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- WRONG — LOWER() on an indexed column
WHERE LOWER(email) = '[email protected]'

-- CORRECT — make the column case-insensitive by collation (once, at table level)
ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_ci;
-- Then: WHERE email = '[email protected]' uses the index

-- WRONG — arithmetic on indexed column
WHERE price * 1.1 > 100

-- CORRECT — move arithmetic to the other side
WHERE price > 100 / 1.1

Fix 4: Fix Composite Index Column Order

MySQL uses a composite index from the leftmost column. The index (a, b, c) can be used for:

• Filter on a
• Filter on a, b
• Filter on a, b, c

But NOT for: filter on b only, filter on c only, filter on b, c.

CREATE INDEX idx_orders ON orders(user_id, status, created_at);

-- Uses the full index (all 3 columns)
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending' AND created_at > '2026-01-01';

-- Uses user_id and status parts (stops at the range column)
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- Uses only user_id (skips status to get to created_at — can't skip)
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01';
-- EXPLAIN: key_len shows only user_id bytes used

-- Does NOT use the index at all (leftmost column not in WHERE)
SELECT * FROM orders WHERE status = 'pending';
-- EXPLAIN: type=ALL

Design composite indexes based on query patterns:

-- Frequent query: filter by status, sort by created_at
-- Create index with status first
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Frequent query: equality on multiple columns + range on one
-- Put equality columns first, range column last
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);
-- Equality: user_id = ?, status = ?  → Range: created_at BETWEEN ? AND ?

Fix 5: Fix LIKE Wildcard Placement

B-tree indexes can only be used for prefix matches. A leading wildcard disables the index:

-- Uses the index — prefix match
SELECT * FROM users WHERE username LIKE 'alice%';
-- EXPLAIN: type=range, key=idx_username ✓

-- Does NOT use the index — leading wildcard
SELECT * FROM users WHERE username LIKE '%alice%';
SELECT * FROM users WHERE username LIKE '%alice';
-- EXPLAIN: type=ALL

-- For full-text search, use a FULLTEXT index instead
ALTER TABLE users ADD FULLTEXT INDEX ft_username (username);
SELECT * FROM users WHERE MATCH(username) AGAINST('alice' IN BOOLEAN MODE);

Alternative: use generated columns for suffix searches:

-- For searching by email domain (suffix)
ALTER TABLE users ADD COLUMN email_reversed VARCHAR(255)
  AS (REVERSE(email)) STORED;
CREATE INDEX idx_email_reversed ON users(email_reversed);

-- Search for @gmail.com emails
SELECT * FROM users WHERE email_reversed LIKE REVERSE('%@gmail.com');
-- ↑ Becomes: email_reversed LIKE 'moc.liamg@%' — prefix match, index used

Fix 6: Update Table Statistics

The optimizer uses statistics (row count, cardinality) to estimate query cost. Stale stats after large data changes cause poor plan choices:

-- Update statistics for a specific table
ANALYZE TABLE orders;
ANALYZE TABLE users;

-- Update statistics for all tables in a database
-- (run from MySQL CLI)
mysqlcheck --analyze --all-databases -u root -p

-- Check current index cardinality
SHOW INDEX FROM orders;
-- Cardinality column: number of unique values estimate
-- Low cardinality on a unique column = stale stats

In MySQL 8.0+, use innodb_stats_auto_recalc — enabled by default, automatically updates stats when >10% of rows change.

Force the optimizer to use a specific index when you know better:

-- USE INDEX — optimizer considers only this index
SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 42;

-- FORCE INDEX — optimizer must use this index (even if it thinks a scan is cheaper)
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 42;

-- IGNORE INDEX — exclude a specific index
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'pending';

Note: Index hints (FORCE INDEX) are a last resort. They tie your query to a specific index that may become suboptimal as data changes. Fix the root cause (statistics, query rewrite, or index design) instead of using hints long-term.

Fix 7: Create Covering Indexes

A covering index includes all columns needed by the query — MySQL can answer the query from the index alone without reading the actual row data:

-- Query needs: user_id (WHERE), status, total (SELECT)
SELECT user_id, status, total
FROM orders
WHERE user_id = 42;

-- Without covering index: index lookup + row fetch for each match
-- With covering index: all data from the index — faster

CREATE INDEX idx_covering ON orders(user_id, status, total);
-- EXPLAIN Extra: "Using index" ← index-only scan, no row fetches

Check if a covering index would help:

EXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- If Extra shows "Using index condition" instead of "Using index",
-- the index exists but doesn't cover all selected columns

-- Adding total to the index makes it a covering index

Still Not Working?

Check the optimizer trace for a detailed explanation of why the optimizer chose a plan:

SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
-- Shows cost estimates for each considered plan — tells you exactly why an index was rejected
SET optimizer_trace = 'enabled=off';

Check for redundant indexes — too many indexes slow down writes and confuse the optimizer:

-- Find duplicate or redundant indexes
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db';

Verify the index actually exists and is on the right column:

SHOW INDEX FROM orders;
-- Check: Column_name, Cardinality, and that the index wasn't dropped

For related database performance issues, see Fix: PostgreSQL Slow Query and Fix: MySQL Slow Query Optimization.