How Indexes Can Slash MySQL Query Time by 30,000× – Real‑World Optimization

Scenario

The database used is MySQL 5.6. Three tables are created:

create table Course(
 c_id int PRIMARY KEY,
 name varchar(10)
)

100 rows of data.

create table Student(
 id int PRIMARY KEY,
 name varchar(10)
)

70,000 rows of data.

CREATE table SC(
    sc_id int PRIMARY KEY,
    s_id int,
    c_id int,
    score int
)

700,000 rows of data.

Query Goal

Find students who scored 100 in Chinese.

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
)

Initial execution time: 30248.271 s. The EXPLAIN plan shows type=ALL (full table scan) and no index usage.

Adding Indexes

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

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 Optimizations

The optimizer rewrites the query to an EXISTS form:

SELECT
    `YSB`.`s`.`s_id` AS s_id,
    `YSB`.`s`.`name` AS name
FROM
    `YSB`.`Student` s
WHERE
    <in_optimizer> (
        `YSB`.`s`.`s_id`, <EXISTS> (
            SELECT 1
            FROM `YSB`.`SC` sc
            WHERE (
                (`YSB`.`sc`.`c_id` = 0) AND
                (`YSB`.`sc`.`score` = 100) AND
                (<CACHE> (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`)
            )
        )
    )

Using this form, MySQL executes the sub‑query first, producing a small result set, then fetches the matching students, reducing the time to 0.001 s.

Join vs Sub‑query

A join version is tested:

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

Without indexes on sc.c_id and sc.score, the join takes 0.057 s. Adding the same single‑column indexes brings the time down to 0.001 s.

Composite Index

When data volume grows (SC table to 3 million rows) and the query filters on both c_id and score, a composite index is created:

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, showing the benefit of a multi‑column index when individual column selectivity is low.

Single‑Column vs Multi‑Column Indexes

Another example uses a table user_test_copy with 3 million rows and filters on sex, type, and age. Separate single‑column indexes yield a plan using type_merge and take 0.415 s. A combined index on the three columns reduces the time to 0.032 s.

Left‑most Prefix

Multi‑column indexes can be used for queries that reference the leftmost columns of the index. Tests show that queries on sex alone, sex + type, or sex + age all use the same composite index because the first column sex is present.

Covering Index

If all selected columns are part of the index, MySQL can return results directly from the index without touching the table rows. Example:

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

With a covering index on (sex, type, age), the query finishes in 0.003 s.

Sorting Index

Ordering results can also benefit from an index:

select * from user_test where sex = 2 and type = 2 ORDER BY user_name

Creating an index on user_name reduces the sorting time from 0.139 s to a much lower value.

Summary

MySQL nested sub‑queries can be very slow.

Rewriting them as joins or EXISTS queries often improves performance.

Filtering tables with WHERE conditions before joining is beneficial, even though MySQL may reorder operations.

Appropriate single‑column and multi‑column indexes are essential; composite indexes give the biggest gains when column selectivity is low.

Analyzing EXPLAIN plans is crucial for understanding and tuning queries.

Index Optimization Tips

Prefer numeric column types with minimal length for primary/foreign keys.

Create single‑column indexes on frequently filtered fields.

When a single filter leaves many rows, add multi‑column (composite) indexes.

Use covering indexes to avoid accessing the base table.

Index columns used in JOIN conditions.

Index columns used in WHERE clauses.

Index columns used for ORDER BY.

Index columns used for GROUP BY.

Avoid functions on indexed columns in WHERE clauses to keep indexes usable.