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

Scenario

The database is MySQL 5.6 with three tables: Course (100 rows), Student (70,000 rows), and SC (700,000 rows).

Query purpose

Find students who scored 100 in the Chinese subject.

Original 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: 30,248.271 s. The EXPLAIN plan shows type=ALL (full table scan) with no index usage.

First optimization – add indexes on filter columns

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

After adding the indexes, the same query runs in 1.054 s – a >30,000× speed‑up.

Further analysis

Even with indexes, the query still takes about 1 s. The plan reveals an EPENDENT SUBQUERY where MySQL evaluates the inner query for each outer row.

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

After dropping the previous indexes and creating an index on SC(s_id), the join runs in 0.057 s, but still slower than the sub‑query with the two single‑column indexes.

Optimized join with filtered sub‑query

SELECT s.*
FROM (
    SELECT *
    FROM SC sc
    WHERE sc.c_id = 0 AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id;

This version executes in 0.054 s, similar to the earlier indexed sub‑query.

Scaling issue

When the SC table grows to 3 million rows and the score distribution becomes more sparse, the query slows down again (≈0.061 s). The plan shows an INTERSECT of two single‑column indexes, indicating low selectivity of each column.

Multi‑column (composite) index

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

With the composite index, the same query runs in 0.007 s.

Index optimization summary

MySQL nested sub‑queries can be very slow.

Rewriting them as joins often improves performance.

Filtering tables before joining (using WHERE) can further reduce work.

Appropriate single‑column indexes help, but composite indexes on the filtering columns give the biggest gains.

Analyzing the EXPLAIN plan is essential to understand how MySQL optimizes a query.

Additional index techniques

Single‑column indexes

Example on a user_test_copy table with columns sex, type, age. Each column indexed separately yields a plan using type=index_merge (INTERSECT) with a query time of 0.415 s.

Multi‑column index

Creating

INDEX user_test_index_sex_type_age ON user_test(sex,type,age)

reduces the same query time to 0.032 s.

Leftmost‑prefix rule

The composite index can be used when the leftmost column(s) appear in the WHERE clause (e.g., sex=2 or sex=2 AND type=2).

Covering index

If all selected columns are part of the index (e.g., SELECT sex,type,age ...), MySQL can return results directly from the index, achieving ~0.003 s.

Sorting

Adding an index on the ORDER BY column ( user_name) speeds up ordered queries.