Six Critical MySQL Index Pitfalls and How to Fix Them

Trap 1: Type Conversion

Problem scene : A query uses mismatched literal types, causing full‑table scans.

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

Fix :

-- Force exact type matching
SELECT * FROM products WHERE category_id = CAST('3' AS SIGNED)
AND status = CAST(1 AS CHAR);

Trap 2: Function Operations

Using functions on indexed columns destroys index order.

SELECT * FROM products WHERE FLOOR(price/100)*100 = 500 -- breaks index ordering

Trap 3: Left‑most Prefix

Composite index idx_cat_status(category,status) becomes ineffective when the query filters only on status: SELECT * FROM products WHERE status = 1; Execution plan shows a full index scan (230 ms) versus a full table scan (380 ms) because the optimizer must perform a row lookup.

Trap 4: Implicit Charset Conversion

A cross‑table join introduces hidden charset conversion, slowing the query.

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

Diagnosis shows mismatched collations; the fix is to unify the charset.

ALTER TABLE users CONVERT TO CHARACTER SET utf8;

Trap 5: Left‑most Match

Composite index idx_time_status(create_time,status) fails when the query filters only on status:

SELECT * FROM logs WHERE status = 'SUCCESS';

Trap 6: Index Selector Mis‑judgment

The optimizer may choose a sub‑optimal plan for queries like:

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

Forcing the correct index and using a covering index resolves the issue:

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

Index Verification Toolkit

# Verify first column of composite index
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%';
# Detect implicit conversions
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

Summary of Six Defense Rules

Exact Type Rule : Ensure WHERE clause literals match column types exactly.

Function Insulation Rule : Avoid wrapping indexed columns with functions.

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

Charset Uniformity Rule : Enforce a consistent character set across the entire database.

Range Right‑Side Ban Rule : Do not place columns after a range condition in the index.

Optimizer Taming Rule : Combine FORCE INDEX with covering indexes when the optimizer mis‑selects.