How to Supercharge MySQL Queries with Index Optimization: A Step‑by‑Step Guide

Scenario

The author uses MySQL 5.6 with three tables: Course, Student, and SC (student scores). The SC table contains 700,000 rows, while Student has 100 rows.

Initial Query and Problem

The goal is to find students who scored 100 in a specific course (c_id = 0). The original query uses a sub‑query:

select s.* from Student s where s.s_id in (
    select s_id from SC sc where sc.c_id = 0 and sc.score = 100
);

Execution time: 30248.271 s . An EXPLAIN shows type=ALL for both tables, meaning full table scans because no indexes are used.

Adding Indexes

Indexes are created on the columns used in the WHERE clause of SC:

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

After adding these indexes, the same query runs in 1.054 s – more than 30,000× faster. The execution plan now uses the new indexes.

Join vs Sub‑query

The author rewrites 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;

When the previous single‑column indexes are dropped, the join takes 1.076 s . Adding back the indexes on c_id and score reduces the time to 0.054 s . The plan shows that MySQL first filters SC using the indexes, then joins the filtered rows to Student.

Composite (Multi‑column) Index

With larger data (SC growing to 3 million rows) the single‑column indexes lose effectiveness. The author creates a composite index on (c_id, score):

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);

The same query now finishes in 0.007 s**, demonstrating the benefit of a covering multi‑column index when the combined selectivity is high.

Final Recommendations

Define columns with the smallest appropriate numeric types.

Create single‑column indexes on frequently filtered fields.

When multiple columns are used together in WHERE, build a composite index covering those columns.

Use covering indexes (include all selected columns) to avoid extra row lookups.

Index join columns, filter columns, and ORDER BY columns to improve join and sorting performance.

Avoid functions or calculations on indexed columns in WHERE clauses, as they prevent index usage.

Key Images (Execution Plans)