How Indexes Can Speed Up MySQL Queries by 30,000×: A Practical Guide

Scenario

The database is 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).

Original Query and Problem

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 30,248 seconds. EXPLAIN showed type=ALL and no index usage.

Adding Indexes

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

After creating the indexes, execution time dropped to 1.054 seconds – a speed‑up of more than 30,000×.

Further Optimization

Even 1 second was considered long. The optimizer had turned the sub‑query into an EXISTS clause, still scanning many rows.

Rewriting the query as an explicit 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;

Execution time became 0.057 seconds. Adding an index on SC.s_id improved it further.

Composite Index

When the SC table grew to 3 million rows, single‑column indexes lost effectiveness. Dropping the previous indexes and creating a composite index solved the issue:

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 seconds.

Single‑Column vs Multi‑Column Indexes

Example with user_test_copy (300 million rows). Single‑column indexes on sex, type, and age gave 0.415 seconds (type=index_merge). A multi‑column index on the three columns reduced time to 0.032 seconds.

create index user_test_index_sex_type_age on user_test(sex, type, age);

Left‑most Prefix Rule

The composite index (sex, type, age) can be used when the query filters on the first column alone or on the first two columns, following MySQL’s left‑most prefix rule.

Covering Index

If the SELECT list contains only indexed columns, MySQL can satisfy the query from the index without touching the table data:

select sex, type, age from user_test
where sex = 2 and type = 2 and age = 10;

Execution time: 0.003 seconds.

Sorting

Creating an index on the ORDER BY column speeds up sorting:

create index user_name_index on user_test(user_name);

Key Takeaways

Analyze EXPLAIN output to spot missing indexes.

Build single‑column indexes on WHERE columns; use composite indexes when multiple columns filter heavily.

Leverage the left‑most prefix rule and covering indexes for maximum efficiency.

Index join columns and ORDER BY columns.

Avoid functions on indexed columns to prevent index loss.