SQL Performance Optimization and Index Tuning in MySQL

The example uses three MySQL tables (Course, Student, SC) with 100, 70,000 and 700,000 rows respectively, and a query that finds students who scored 100 in a specific course. The original sub‑query runs for over 30,000 seconds because the optimizer chooses a full table scan (type=ALL) and does not use any indexes.

First, indexes are created on the SC(c_id) and SC(score) columns. After adding these indexes, the same query finishes in about 1 second, a 30,000‑fold improvement.

Further analysis shows that MySQL rewrites the sub‑query into an EXISTS form and still performs a costly scan. Rewriting the query as an inner join: SELECT s.* FROM Student s INNER JOIN SC sc ON sc.s_id = s.s_id WHERE sc.c_id = 0 AND sc.score = 100; still takes several hundred milliseconds, so an index on SC(s_id) is added. With SC(s_id) indexed, the join runs in 0.057 s, but the plan reveals that MySQL still scans many rows.

To force the optimizer to filter SC first, a derived table is used: SELECT s.* FROM (SELECT * FROM SC WHERE c_id = 0 AND score = 100) t INNER JOIN Student s ON t.s_id = s.s_id; This reduces the execution time to 0.001 s.

When data volume grows to 3 million rows, the single‑column indexes become less selective. Creating a composite index SC(c_id, score) drops the query time to 0.007 s, demonstrating the importance of multi‑column indexes for high‑cardinality filters.

The article then shifts to another table user_test_copy with columns sex , type , and age . Separate single‑column indexes yield a 0.415 s query using the type=index_merge strategy. Adding a three‑column index user_test(sex, type, age) speeds the same query to 0.032 s, illustrating the “left‑most prefix” rule.

Additional tips include using covering indexes (selecting only indexed columns), indexing columns used for ORDER BY, and avoiding functions on indexed columns to prevent index loss.

Overall, the guide emphasizes analyzing execution plans, adding appropriate single‑ and multi‑column indexes, and rewriting queries to let MySQL exploit those indexes for optimal performance.