Boost MySQL Query Speed: Index Tricks, Join vs Subquery, and Execution Plan Hacks

Scenario

I am using MySQL 5.6 and will briefly describe the test data.

Tables

Course table

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

Student table

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

Student scores table (SC)

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

Data: 100 rows in Course, 70,000 rows in Student, 700,000 rows in SC.

Initial Query

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 – extremely slow.

First Optimization: Add Indexes

The query plan shows a full table scan (type=ALL). Adding indexes on the WHERE columns improves performance.

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

New execution time: 1.054 s – over 30,000× faster.

Further Tuning

Inspecting the plan reveals that MySQL still executes the sub‑query first, causing many row combinations. Rewriting as a join and ensuring the filter is applied before the join yields better results.

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;

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 indexed sub‑query because the join lacks optimal indexes.

Composite Indexes

Because the combination of c_id and score has high selectivity, a composite index 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 drops to 0.007 s , an acceptable speed for large tables.

Additional Topics Covered

Single‑column indexes vs. multi‑column (composite) indexes.

Index merge (type=index_merge) when multiple single‑column indexes are used.

Covering indexes: selecting only indexed columns avoids reading the full row.

Sorting optimization: creating an index on the ORDER BY column reduces sort time.

Summary

Nested sub‑queries in MySQL can be very slow.

Rewrite them as joins when possible.

Apply WHERE filters before joining to limit row sets.

Build appropriate single‑column indexes and, when needed, multi‑column composite indexes.

Analyze EXPLAIN output to understand MySQL’s optimizer decisions.