Boost MySQL Query Speed: Indexing, Subqueries vs Joins Explained

Scenario

We use MySQL 5.6 with three tables: Course (100 rows), Student (70,000 rows) and SC (700,000 rows) that stores student scores.

Initial Query and Problem

The goal is to find students who scored 100 in the Chinese subject (c_id = 0). The 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
)

took 30,248 seconds. An EXPLAIN showed type=ALL for all tables, meaning no indexes were used.

Adding Single‑Column Indexes

Creating indexes on the filter columns

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

reduced the execution time to 1.054 seconds – a >30,000× improvement.

Join Optimization

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

executed in 0.057 seconds. Adding an index on SC(s_id) unexpectedly increased the time to 1.076 seconds because the optimizer chose a less efficient plan.

Composite Index

Dropping the single‑column indexes and creating a 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);

brought the execution time down to 0.007 seconds.

Summary of Findings

Nested sub‑queries can be very slow; converting them to joins is usually faster.

MySQL may reorder operations, applying WHERE filters before joins when it reduces row counts.

Appropriate indexes on filter and join columns are essential.

Multi‑column (composite) indexes often outperform multiple single‑column indexes, especially on large tables with low column selectivity.

Single‑Column Index Example

On a table user_test_copy with 3,000,000 rows, indexes were created on sex, type and age. The query

select * from user_test_copy where sex = 2 and type = 2 and age = 10;

took 0.415 seconds and used type=index_merge (MySQL intersected the three indexes).

Multi‑Column Index Example

Creating a composite index

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

reduced the same query time to 0.032 seconds – more than tenfold faster.

Left‑most Prefix Rule

A composite index can be used as long as the left‑most columns appear in the WHERE clause. Queries filtering on sex alone or on sex and type both benefit from the same index.

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;

executed in 0.003 seconds.

Sorting Index

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

create index user_name_index on user_test(user_name);

The query with ORDER BY user_name dropped from 0.139 seconds to a much lower value.

Key Takeaways

Prefer joins over nested sub‑queries for large datasets.

Filter rows first, then join.

Create single‑column indexes on frequently filtered fields.

When single‑column indexes are insufficient, use composite indexes.

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

Index columns used in ORDER BY and GROUP BY.

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