Does SELECT * Always Invalidate Indexes? Why Some Teams Ban It

During a backend interview, an interviewer asked whether SELECT * always leads to index failure. The answer is “not necessarily”; MySQL’s optimizer decides based on cost, and only abandons an index when a full table scan is estimated to be cheaper.

SELECT * Query (Cost Trade‑off)

Core definition: SELECT * itself does not directly invalidate an index. If the WHERE clause can use an index, the optimizer will consider it.

Return‑cost decision: When the result set proportion exceeds roughly 20‑30% of the table, MySQL may deem a full scan (sequential I/O) cheaper than index lookup plus row‑fetch (random I/O) and will voluntarily drop the index.

Recommendation: Avoid blind use of SELECT * in production; prefer covering indexes and select only required columns.

Violating the Left‑most Prefix Principle

Core definition: A composite index is used only if the query conditions match the index’s leftmost columns in order.

Range‑query effect: Once a range condition (e.g., BETWEEN, LIKE 'abc%') appears, columns to its right cannot be used for index positioning because the B+Tree order is broken.

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

Recommendation: Do not rely on ISS to compensate for poor index design; adjust the composite index order or add missing leading‑column conditions.

Functions or Type Conversion on Indexed Columns

Core definition: Indexes store the original column values. Applying a function (e.g., ABS(), DATE()) or arithmetic operation in the WHERE clause changes the logical value, breaking the index order.

Order loss effect: The B+Tree becomes unordered for the transformed values, forcing a full table scan.

Functional index: MySQL 8.0 supports functional indexes, but they should be used sparingly; rewriting the query to avoid the function is usually preferable.

Example:

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

Optimization: Move the calculation to the constant side:

SELECT * FROM students WHERE height = 169;
SELECT * FROM students WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59';

LIKE with Leading Wildcard

Core definition: An index can be used only when the pattern starts with a concrete prefix, e.g., WHERE name LIKE 'Guide%'. A leading % destroys the ordered nature of the B+Tree.

Failure mechanism: Patterns like LIKE '%abc' or LIKE '%abc%' require scanning the entire index (type index) or the whole table (type ALL).

Recommendation: For full‑text fuzzy search, use a dedicated search engine such as Elasticsearch; if a wildcard is unavoidable, ensure the query only touches indexed columns so the plan shows type: index (index full scan) instead of ALL.

OR and Index Merge

Core definition: If any side of an OR condition lacks an index, MySQL will drop all indexes and perform a full scan.

Index Merge mechanism: When both sides have indexes, MySQL 5.1+ may use Index Merge, scanning each index separately and merging the results. If the filtered row count is large, the merge cost can exceed a full scan, causing the optimizer to abandon indexes.

Recommendation: Rewrite OR as UNION ALL (when duplicate rows are not a concern) so each subquery can use its own index without the optimizer’s cost‑estimation uncertainty.

IN / NOT IN Misuse

eq_range_index_dive_limit: The default limit (200) controls whether MySQL uses precise Index Dive for row‑count estimation. ≤200 uses Index Dive, keeping the index effective; >200 switches to statistics‑based estimation, which can misjudge costs and lead to a full scan.

Work‑around: Increase the limit or rewrite the IN list as a temporary table join.

NOT IN: Constant lists usually cause a full scan. Subqueries that reference indexed columns can still use the index. Prefer NOT EXISTS or LEFT JOIN … IS NULL for better performance and clearer semantics.

Implicit Type Conversion

Conversion on the indexed column (e.g., varchar_col = 123) forces MySQL to cast the column to DOUBLE, breaking the B+Tree order and invalidating the index.

Conversion on the constant side (e.g., int_col = '123') is safe; the constant is cast, leaving the index usable.

Reference: MySQL documentation rule 7 explains that string‑to‑number comparison converts the string to DOUBLE, which behaves like an irreversible function on the indexed column.

ORDER BY Sorting Pitfalls

Using ORDER BY on a column not covered by the index forces a filesort.

When the index order differs from the ORDER BY clause (e.g., index (a,b) but ORDER BY b,a), MySQL cannot use the index for sorting.

If WHERE and ORDER BY use different indexes, the optimizer may still need a filesort.

Sorting on non‑indexed columns selected by SELECT * requires a row‑fetch and then a filesort.

Optimization: Use a covering index that satisfies both WHERE and ORDER BY, or adjust the index order to match the desired sort.

Verification method: In EXPLAIN , the presence of type: index_merge with Extra: Using union; Using where indicates Index Merge is active, while Extra: Using filesort shows a sorting penalty.

References

[1] Bug #109145 – https://bugs.mysql.com/bug.php?id=109145

[2] MySQL documentation rule 7 – https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

[3] “MySQL Implicit Conversion Causing Index Invalidation” – https://javaguide.cn/database/mysql/index-invalidation-caused-by-implicit-conversion.html