How Indexes Transform MySQL Subquery Performance: A Deep Dive

Scenario

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

Initial Query and Problem

Goal: find students who scored 100 in Chinese (c_id = 0).

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: 30248.271 s . The EXPLAIN plan shows type=ALL for all tables – no indexes are used.

First Optimization: Single‑Column Indexes

Create indexes on the columns used in the WHERE clause of SC:

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

New execution time: 1.054 s – a >30,000× speedup.

Further Tuning: Query Rewrite

Inspect the plan and rewrite the query using an EXISTS -style subquery (MySQL internally transforms it):

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

Running the inner subquery alone takes 0.001 s , confirming the index usage.

Join Approach

Try an inner join without indexes on SC:

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: 0.057 s . Adding an index on SC(s_id) unexpectedly increased the time to 1.076 s due to a different plan.

Derived Table to Force Filter‑First

Force MySQL to filter SC first, then join:

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;

Execution time drops to 0.054 s . Restoring the two single‑column indexes on SC(c_id) and SC(score) brings the time down further to 0.001 s (≈50× faster).

Scaling Issue and Composite Indexes

When SC grows to 3 M rows, the same query slows to 0.061 s . Single‑column indexes on c_id and score are merged ( type=index_merge) but still scan many rows.

Creating a composite index on the two filter columns dramatically improves performance:

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

Execution time: 0.007 s .

Single‑Column vs Multi‑Column Indexes

Testing on another table user_test (300 M rows) shows:

Three single‑column indexes on sex, type, age → query time 0.415 s (index_merge).

One composite index (sex, type, age) → query time 0.032 s (≈10× faster).

The left‑most prefix rule applies: the composite index can be used for queries that filter on the first column alone, or the first two columns, etc.

Covering Indexes

If the SELECT list contains only indexed columns, MySQL can satisfy the query from the index alone (covering index), e.g.:

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

Execution time: 0.003 s , much faster than retrieving all columns.

Indexing for Sorting

Ordering by a column that is indexed avoids an extra sort step:

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

With an index on user_name, the query runs in 0.139 s instead of a full filesort.

Key Takeaways

MySQL subqueries can be extremely slow without proper indexes.

Rewriting subqueries as joins or using derived tables can force more efficient execution order.

Build appropriate single‑column indexes on filter fields; when selectivity is low, add multi‑column (composite) indexes.

Use covering indexes to avoid fetching full rows.

Index join columns, WHERE columns, ORDER BY columns, and GROUP BY columns.

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