Why MySQL Scanned Millions of Rows Despite an Index

MySQL can scan millions of rows even when an index exists because the query’s conditions, data types, or table statistics prevent the optimizer from using that index efficiently—or because using it would actually be slower than a full scan. The most common causes are non-sargable expressions on indexed columns, a low-cardinality index, a hidden type conversion, or an optimizer choosing a full scan based on stale statistics. Below we break down the underlying mechanics and show you how to diagnose and fix each scenario.

How MySQL Decides to Use an Index

MySQL’s optimizer estimates the cost of several execution plans and picks the one it believes is cheapest. For each candidate index, it estimates how many rows it would need to read, using statistics stored per index (cardinality, number of distinct values) and per table (row count). If the estimated rows to read are a large fraction of the table, the optimizer often prefers a full table scan because reading the whole table sequentially is faster than jumping around in the index and then fetching rows randomly from the heap.

The key metric is selectivity: an index is only helpful when it narrows the result set significantly. If your query matches 30% of rows, the optimizer may still use the index, but if it matches 80%, it will likely scan the table. The exact threshold depends on table size, index size, and server configuration (e.g., eq_range_index_dive_limit).

Non-Sargable Conditions: The Index Can’t Be Used

A condition is non-sargable when the indexed column is wrapped in a function or arithmetic operation. The index stores raw column values, not the result of a function, so MySQL cannot look up the value directly. It must compute the function for every row, which forces a full index scan or table scan.

Common examples:

• WHERE YEAR(created_at) = 2024 — use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
• WHERE price * 1.1 > 100 — rewrite as WHERE price > 100 / 1.1
• WHERE CONCAT(first_name, ' ', last_name) = 'John Doe' — avoid this, store a separate column if needed
• WHERE DATE(created_at) = CURDATE() — use a range on created_at

Also, a LIKE pattern that starts with a wildcard (%abc) cannot use a B-tree index because the index is sorted from the left. Only a suffix match (abc%) can use the index.

Type Mismatch and Collation Issues

When the column type and the comparison value type differ, MySQL applies an implicit conversion. For example, if an indexed column is VARCHAR and you compare it to a number, MySQL converts the column to a number—so the column is wrapped in a conversion function, making the condition non-sargable.

-- indexed column is VARCHAR(20)
SELECT * FROM users WHERE phone = 1234567890;  -- converts phone to number
-- fix: use a string literal
SELECT * FROM users WHERE phone = '1234567890';

Similarly, comparing a utf8mb4 column to a latin1 string can cause a collation mismatch that prevents index use. Ensure both sides use the same collation, or use COLLATE explicitly.

Low Cardinality: When the Index Is Useless

Cardinality is the number of distinct values in an indexed column. If a column has very few distinct values (e.g., a boolean flag or a status with 3 values), the index has low selectivity. Querying for one value might match 30% of rows, and the optimizer will likely scan the table because the index would not reduce the number of rows enough to justify the extra I/O.

In such cases, a composite index that includes the low-cardinality column plus another selective column can help. For example, an index on (status, created_at) allows efficient filtering by status and then sorting/range on date.

Stale Statistics and the Optimizer's Guess

MySQL relies on table statistics to estimate row counts. If the statistics are outdated (e.g., after a large batch insert or delete), the optimizer may think an index is not selective enough and choose a full scan. Running ANALYZE TABLE updates these statistics.

Also, the eq_range_index_dive parameter controls how the optimizer estimates the number of rows for IN lists. For a large list, it may use a rough estimate instead of diving into the index, leading to a poor plan.

How to Diagnose: EXPLAIN and the Query Cost

Always start with EXPLAIN on your query. Look at the type column: ALL means full table scan, index means full index scan, range means index range scan, and ref or eq_ref are good. Also check key (which index is used), rows (estimated rows), and Extra (e.g., Using where or Using index condition).

If the optimizer chooses a full scan but you believe an index is better, you can use FORCE INDEX to test. But be careful—forcing an index when the optimizer is right can make performance worse. Instead, try rewriting the query, updating statistics, or creating a better composite index.

Common Misconceptions and Pitfalls

• “I have an index, so it must be used.” Not true—the optimizer decides based on cost.
• “Indexing every column helps.” Too many indexes slow down writes and can confuse the optimizer.
• “A composite index on (a,b) also helps queries on b alone.” It does not; the leftmost prefix rule applies.
• “Using OR in conditions can use an index.” Only if each side of the OR can use an index; otherwise, full scan.
• “NULL values are not indexed.” In MySQL, NULL is indexed, but a query like WHERE col IS NULL can use the index only if the optimizer chooses to.

Frequently Asked Questions

Why does EXPLAIN show 'rows' as millions even though I have an index?

The rows column is an estimate. If the optimizer thinks the index will still match a large percentage of rows, it will show a high number and likely choose a full scan. Update statistics with ANALYZE TABLE and check if your condition is sargable.

Can I force MySQL to use an index?

Yes, with SELECT * FROM table FORCE INDEX (index_name) WHERE .... But this is a temporary fix; the optimizer may be right. Use it to test, then fix the root cause.

Does adding an index always speed up SELECT queries?

No. If the index is not selective, it may be ignored. Also, each index adds overhead to INSERT/UPDATE/DELETE. Only add indexes that support your most frequent queries.

What is a covering index?

A covering index contains all columns needed for the query, so MySQL can answer the query entirely from the index without reading the table row. This is very fast and shows Using index in EXPLAIN.

Why does my query use a full scan even when the condition is on an indexed column?

Check for non-sargable expressions, type conversions, or low cardinality. Also, if the table is small, the optimizer may prefer a full scan because it's cheaper than index lookups.

How do I know if my index is selective enough?

Run SELECT COUNT(DISTINCT col) / COUNT(*) FROM table for the column. If the ratio is close to 1 (e.g., 0.9), it's highly selective. If it's below 0.1, the index may not help much.

Related

• Galaxy S9 Iris Scanner: Does It Have One?
• Expand RAID5 Partition: How to Do It Safely
• IPTV USA: Compare Channels, Costs & Devices