When Does SELECT * Break Your MySQL Index? A Deep Dive into Index Invalidation

SELECT * Cost Trade‑off

SELECT * itself does not directly cause index loss; it is a non‑covering index query. If the WHERE clause can use an index, the optimizer first considers an index scan plus a possible back‑table lookup. When the estimated row count exceeds roughly 20‑30% of the table, the optimizer may deem a full table scan cheaper and abandon the index.

Core definition : SELECT * does not automatically invalidate indexes.

Back‑table cost decision : MySQL compares "index scan + back‑table" vs "full scan" and may drop the index if the back‑table cost is high.

Practical advice : Avoid SELECT * in production; use covering indexes and request only needed columns, ensuring the Extra column shows Using index instead of Using where.

Violating the Left‑most Prefix Rule

MySQL uses the left‑most prefix of a composite index. If a query does not start with the leftmost indexed column, the index cannot be used for the remaining columns. Range conditions (e.g., BETWEEN, LIKE 'abc%') break the ordered nature, preventing subsequent columns from being used.

Core definition : The left‑most prefix principle requires the query to match the first indexed column.

Index Skip Scan (ISS) : Introduced in MySQL 8.0.13, ISS enumerates distinct values of the leading column to scan later columns when the prefix is missing. In MySQL 8.0.31 a serious bug (Bug #109145) can cause data loss during range reads.

Recommendation : Do not rely on ISS to fix poor index design; reorder composite indexes or add missing leading columns.

Functions, Calculations, and Type Conversion on Indexed Columns

Applying functions (e.g., ABS(), DATE()) or arithmetic on indexed columns changes the logical value, breaking the B‑Tree ordering and forcing a full scan. MySQL 8.0 supports functional indexes, but they should be used sparingly.

Core definition : Indexes store raw column values; any transformation on the indexed column makes the index unusable.

Examples :

SELECT * FROM students WHERE height + 1 = 170;  -- function on indexed column → full scan
SELECT * FROM students WHERE DATE(create_time) = '2022-01-01';  -- function on indexed column → full scan

Optimization : Move calculations to the constant side or rewrite using a range condition, e.g., WHERE height = 169 or WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59' .

LIKE with Leading Wildcard

A LIKE pattern that starts with % cannot use the index because the B‑Tree is ordered from left to right. Only prefix patterns (e.g., WHERE name LIKE 'Guide%') can benefit from the index.

Failure example :

SELECT * FROM students WHERE sname LIKE '%Guide';   -- full scan
SELECT * FROM students WHERE sname LIKE '%Guide%';  -- full scan

Advice : For full‑text fuzzy search, use a dedicated search engine such as Elasticsearch; otherwise, ensure the pattern has a fixed prefix.

OR Conditions and Index Merge

If any operand of an OR lacks an index, MySQL may abandon all indexes and perform a full scan. When both sides have usable indexes, MySQL 5.1+ can apply the Index Merge optimization, scanning each index separately and merging the results.

Core definition : Any missing index in an OR forces a full scan.

Index Merge : Works when both sides are indexed, but may be slower than a full scan if each side returns many rows.

Practical tip : Rewrite OR as UNION ALL when result sets are guaranteed distinct, allowing each subquery to use its own index.

SELECT * FROM students WHERE sname = '学生 1'
UNION ALL
SELECT * FROM students WHERE address = '上海';

IN / NOT IN Pitfalls

The optimizer parameter eq_range_index_dive_limit (default 200) controls whether MySQL performs an exact index dive for IN lists. Short lists (< = 200) are estimated accurately; longer lists switch to statistics‑based estimation, which can mislead the optimizer into a full scan.

Large IN list may cause the optimizer to abandon the index. NOT IN on constant lists almost always triggers a full scan; prefer NOT EXISTS or LEFT JOIN … IS NULL . Example : <code>SELECT * FROM students WHERE s_code IN (1,2,3,…,500); -- may mis‑estimate SELECT * FROM students WHERE s_code NOT IN (1,2,3); -- full scan</code>

Implicit Type Conversion

When a string column is compared to a numeric literal, MySQL converts the string to a floating‑point number (DOUBLE) on the indexed column side, breaking the index. Converting the constant side (e.g., int_col = '123') does not affect the index.

Key point : Only conversions that occur on the indexed column invalidate the index.

Example :

-- Index loss
WHERE varchar_col = 123   -- string column converted to DOUBLE
-- Index remains
WHERE int_col = '123'     -- constant converted, index usable

ORDER BY Sorting Traps

Even with an optimal WHERE clause, a poorly matched ORDER BY can force MySQL to use Using filesort, leading to slow queries.

Sorting column not covered by an index.

Index order differs from ORDER BY (e.g., index (a,b) but ORDER BY b,a). WHERE and ORDER BY use different indexes, causing a second lookup.

Sorting on a non‑indexed column selected with SELECT * forces a back‑table sort.

Optimization :

Use a covering index that satisfies both WHERE and ORDER BY, e.g., index (name, age) with SELECT name, age FROM users WHERE name='A' ORDER BY age.

Adjust the index column order to match the required sort.

Verification : Run EXPLAIN and check the Extra column for Using filesort, type: index_merge, or Using union; Using where to confirm which optimizations are applied.