Six Common MySQL Index Pitfalls and Their Remedies

Continuing from the previous story, Lin Yuan notices a sudden spike in QPS (18500) and sees the Handler_read_next counter exploding, indicating a full‑table scan. The technical director warns that degrading the recommendation system would be disastrous during the live event.

Trap 1: Type Conversion

Failure scenario:

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

Fix: Force exact type matching using CAST.

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

Trap 2: Function Operations

Price range query using FLOOR destroys index order:

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

Trap 3: Left‑Prefix Violation

Composite index idx_cat_status(category,status) becomes ineffective when the leading column is omitted:

SELECT * FROM products 
WHERE status = 1

Execution plan shows full index scan (230 ms) versus full table scan (380 ms) due to the need for a table lookup.

Trap 4: Implicit Charset Conversion

Cross‑table join hidden charset mismatch:

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

Solution: Convert the table charset to UTF‑8.

ALTER TABLE users CONVERT TO CHARACTER SET utf8;

Trap 5: Left‑Prefix Misuse

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

SELECT * FROM logs 
WHERE status = 'SUCCESS'

Trap 6: Index Selector Misjudgment

Optimizer chooses a sub‑optimal plan; forcing the correct index improves performance:

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

Index Verification Toolkit

# Verify index effectiveness
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

Lin Yuan distilled six defensive rules for 2003‑era MySQL performance:

Type Precision Rule: WHERE clauses must match column types exactly.

Function Insulation: Avoid wrapping indexed columns with functions.

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

Charset Unification: Enforce a uniform charset across the database.

Range Right‑Side Ban: Columns after a range condition are excluded from index usage.

Optimizer Taming: Use FORCE INDEX together with covering indexes when necessary.