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

<code>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
)</code>

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

<code>CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);</code>

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

<code>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;</code>

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

<code>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;</code>

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

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

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.