MySQL Query Optimization and Index Tuning: From Subqueries to Composite Indexes

The author presents a MySQL 5.6 scenario with three tables (Course, Student, SC) and a query that finds students scoring 100 in a specific course, initially taking over 30,000 seconds due to a full‑table scan.

After examining the EXPLAIN output, the lack of indexes (type=ALL) is identified, leading to the creation of indexes on SC(c_id) and SC(score) :

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

With these indexes the query time drops to about 1 second, but further tuning is explored.

By rewriting the subquery as an EXISTS clause and later as an inner join, and by adding an index on SC(s_id) , execution time is reduced to around 0.05 s. The author also shows how MySQL may reorder operations, executing WHERE filters before joins when it reduces row volume.

When data volume grows (SC table to 3 million rows) and selectivity decreases, a composite index on SC(c_id, score) is created:

ALTER table SC drop index sc_c_id_index;
ALTER table SC drop index sc_score_index;
CREATE index sc_c_id_score_index on SC(c_id, score);

This further improves performance to milliseconds. The article also covers single‑column vs. multi‑column indexes, the left‑most prefix rule, covering indexes, and indexing columns used for sorting.

Key takeaways include: avoid costly nested subqueries, prefer joins, create selective indexes (including composite ones), use covering indexes for read‑only queries, and always analyze the execution plan to verify optimizer decisions.