How Indexes Can Speed Up MySQL Queries by Tens of Thousands of Times

Scenario

We use MySQL 5.6 with three tables: Course (100 rows), Student (70,000 rows) and SC (700,000 rows). The goal is to find students who scored 100 in the Chinese subject (c_id = 0).

Initial Query and Performance

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

The query took 30248.271 s . An EXPLAIN showed type=ALL for both tables, meaning no indexes were used.

Index Creation

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 ran in 1.054 s , a >30,000× speed‑up.

Join Optimization

We rewrote the query as a 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;

Removing the previous indexes and adding an index on SC(s_id) gave an execution time of 0.057 s . Adding back the c_id and score indexes further reduced the time to 0.001 s .

Composite Index

When data grew to 3 million rows, a single‑column index on each filter column became less selective. Creating a composite index on both columns improved performance:

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 query now runs in 0.007 s .

Single‑Column vs Multi‑Column Indexes (User Test Example)

Table user_test_copy (3 million rows) was queried with three equality conditions. Separate indexes on sex, type, and age gave a runtime of 0.415 s and an index_merge plan.

Creating a multi‑column index user_test_index_sex_type_age reduced the runtime to 0.032 s . The left‑most prefix rule applies: the first column of the composite index must appear in the WHERE clause.

Covering indexes (indexing all selected columns) further speed up queries, e.g., selecting only sex, type, age with the composite index runs in 0.003 s .

Sorting Optimization

Ordering by user_name without an index took 0.139 s . Adding an index on user_name improves sorting performance.

Best‑Practice Summary

MySQL nested sub‑queries can be very slow; prefer joins.

Apply WHERE filtering before joining when possible.

Build appropriate single‑column indexes on filter and join columns.

Use multi‑column (composite) indexes when multiple columns are used together and have low individual selectivity.

Leverage covering indexes to avoid fetching full rows.

Index columns used in ORDER BY and GROUP BY.

Avoid functions on indexed columns in WHERE clauses to prevent index loss.