Six Fatal MySQL Index Traps and How to Avoid Them

Background

During a high‑traffic event the system saw QPS of 18,500 and the Handler_read_next counter increase by millions per second, a clear sign of a full‑table scan.

Trap 1: Type Conversion

Using mismatched data types in WHERE clauses forces implicit conversion and disables index usage.

SELECT * FROM products
WHERE category_id = '3'   -- column is INT
AND status = 1               -- column is ENUM('0','1')

Fix by casting to the exact type of each column:

SELECT * FROM products
WHERE category_id = CAST('3' AS SIGNED)
AND status = CAST(1 AS CHAR);

Trap 2: Function on Indexed Column

Applying a function to an indexed column breaks the index’s ordering, causing a full scan.

SELECT * FROM products
WHERE FLOOR(price/100)*100 = 500;  -- destroys index order

Solution: store the derived value in a separate column or rewrite the condition to avoid the function.

Trap 3: Left‑most Prefix

A composite index can be used only if the query predicates include the left‑most column(s). Example of a failing query: SELECT * FROM products WHERE status = 1; The index idx_cat_status(category,status) is ignored because the leftmost column category is not referenced.

Resulting B+‑tree scan shows a full‑table scan is slower than a full‑index scan.

# Execution time comparison
Full index scan: 230 ms
Full table scan: 380 ms  # requires row lookup (回表)

Trap 4: Implicit Character Set Conversion

Joining tables with different character sets triggers hidden conversions that prevent index usage.

SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.name = '林渊';

Detect charset mismatches via:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE COLLATION_NAME NOT LIKE 'utf8%';

Fix by converting the table to a unified charset:

ALTER TABLE users CONVERT TO CHARACTER SET utf8;

Trap 5: Range Query Right‑Side Exclusion

When a range condition is applied to the leftmost column of a composite index, columns to the right cannot be used for index lookups.

SELECT * FROM logs
WHERE status = 'SUCCESS';

If the index is idx_time_status(create_time,status) and a preceding range on create_time exists, the status part is ignored.

Trap 6: Index Selector Mis‑judgment

The optimizer may choose a full scan for a query that could use an index, especially when ordering conflicts with the index.

SELECT * FROM products
WHERE category_id = 3 AND is_hot = 1
ORDER BY price DESC;

Force the desired index and enable covering index usage:

SELECT * FROM products FORCE INDEX(idx_category)
WHERE category_id = 3 AND is_hot = 1
ORDER BY price DESC;

Index Verification Toolkit

Check index column order:

SHOW INDEX FROM products WHERE Seq_in_index = 1;

Detect charset conflicts:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE COLLATION_NAME NOT LIKE 'utf8%';

Find implicit conversions:

EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;  -- look for conversion warnings

Summary of Defensive Rules

Exact Type Rule: Column types must match the literals in WHERE clauses.

Function Insulation: Do not wrap indexed columns with functions.

Left‑most Prefix Rule: The first column of a composite index must appear in the query predicates.

Charset Uniformity: Ensure a single character set (e.g., utf8) across the whole database.

Range Right‑Side Exclusion: Columns to the right of a range condition cannot be used by the index.

Optimizer Taming: Use FORCE INDEX and covering indexes when the optimizer makes a poor choice.