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 builds multiple candidate execution plans, assigns each a cost based on the number of rows it expects to read, the number of random I/O operations required, and the overhead of traversing the B-tree, then picks the cheapest plan. An index that looks useful to a developer may be legitimately more expensive than a full table scan when the optimizer’s math says so.

The most common reasons the optimizer skips an index break down into two categories: situations where the index cannot be used, and situations where it could but the optimizer decides a scan is cheaper. In the first category, type mismatches (implicit conversions) force MySQL to evaluate a cast for every row, making the B-tree unsearchable. Functions applied to the indexed column have the same effect: WHERE DATE(created_at) = '2026-01-01' computes a new value per row, and the index stores raw created_at values. Skipping the leading column of a composite index also falls here because MySQL can only walk the B-tree from left to right.

In the second category, low cardinality is the main driver. If a status column has 3 distinct values in a 1M-row table, the optimizer estimates that an index lookup would return 333K rows and then require 333K random disk seeks to fetch the full rows. A sequential full table scan is nearly always faster. Outdated statistics cause the optimizer to miscalculate cardinality and choose the wrong plan. LIKE with a leading wildcard prevents B-tree traversal entirely. Small tables (a few hundred rows) are also scanned directly because the entire table fits in a single I/O operation.

Platform and Environment Differences

The optimizer’s behavior, diagnostic tools, and default settings vary significantly across MySQL versions, forks, and hosting platforms.

MySQL 5.7 vs 8.0 vs 8.4. MySQL 8.0 introduced the EXPLAIN ANALYZE statement, which shows actual execution time alongside estimated costs. In 5.7, you only get cost estimates, so diagnosing optimizer misjudgments requires running SET profiling = 1, executing the query, and calling SHOW PROFILES. MySQL 8.0 also introduced histogram statistics (ANALYZE TABLE ... UPDATE HISTOGRAM ON column), which give the optimizer better cardinality estimates for skewed data distributions. Without histograms, the optimizer assumes uniform distribution and frequently overestimates the number of matching rows, leading to index skips. MySQL 8.0.27+ added invisible indexes (ALTER INDEX idx INVISIBLE), which lets you test whether removing an index would change the plan without actually dropping it. MySQL 8.4 (LTS) improved cost model defaults for NVMe storage, making index lookups relatively cheaper compared to full scans — queries that skipped an index on 8.0 may use it on 8.4 without any change to the schema.

MariaDB optimizer differences. MariaDB’s optimizer diverges from MySQL’s in several ways. MariaDB supports index_merge more aggressively and uses it in cases where MySQL would fall back to a full scan. MariaDB 10.4+ supports ANALYZE FORMAT=JSON with per-step timing, similar to MySQL 8.0’s EXPLAIN ANALYZE but with different output structure. MariaDB’s optimizer_switch has flags like index_merge_sort_intersection that MySQL does not have. If you are migrating between MySQL and MariaDB, compare SHOW VARIABLES LIKE 'optimizer_switch' and expect different plan choices for the same schema.

RDS vs self-hosted optimizer_switch defaults. AWS RDS for MySQL and Aurora MySQL ship with optimizer_switch values that differ from the stock MySQL defaults. Aurora’s query optimizer uses a custom cost model tuned for its distributed storage layer, where random I/O is cheaper relative to sequential I/O than on local disks. This means Aurora is more likely to use an index than self-hosted MySQL on the same data. RDS MySQL also enables batched_key_access by default in some parameter groups, which affects join plan selection. Google Cloud SQL and Azure Database for MySQL use near-stock defaults. Always check SHOW VARIABLES LIKE 'optimizer_switch' on managed instances and compare with your local development database.

EXPLAIN format differences per client. The MySQL CLI (mysql) shows EXPLAIN output as a table by default. MySQL Workbench renders it as a visual diagram with color-coded cost indicators. DBeaver and DataGrip format EXPLAIN FORMAT=JSON output into collapsible trees. pt-visual-explain from Percona Toolkit renders a text-based tree. When sharing EXPLAIN output in a team, use EXPLAIN FORMAT=JSON for consistency — the tabular format truncates long Extra values and hides nested loop details that JSON preserves.

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 uses 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

-- MySQL 8.0.13+: functional indexes
CREATE INDEX idx_lower_email ON users ((LOWER(email)));
-- Now WHERE LOWER(email) = '[email protected]' uses the functional 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

Collation mismatch across joined tables — if two tables use different collations (e.g., utf8mb4_unicode_ci vs utf8mb4_general_ci), a join on their string columns forces MySQL to convert values on every row comparison, skipping indexes on both sides. Verify collations with SHOW CREATE TABLE and standardize.

Partitioned tables and local vs global indexes — MySQL only supports local indexes on partitioned tables. If the partition key is not part of the query’s WHERE clause, MySQL must scan all partitions. Use EXPLAIN PARTITIONS (5.7) or EXPLAIN (8.0+, shows partitions automatically) to check which partitions are pruned.

ORM-generated queries adding hidden casts — ORMs like Hibernate, SQLAlchemy, and ActiveRecord sometimes send parameters with types that differ from the column type. Enable the general query log (SET GLOBAL general_log = 'ON') temporarily, capture the actual SQL your ORM sends, and run EXPLAIN on it. The query you see in your application code may differ from what reaches MySQL.

For related database performance issues, see Fix: PostgreSQL Slow Query, Fix: MySQL Slow Query Optimization, Fix: MySQL Full-Text Search Not Working, and Fix: PostgreSQL Index Not Used.