MySQL Query Optimization and Index Tuning: From Subqueries to Joins

The article describes a performance problem in MySQL where a query to locate students who scored 100 in Chinese took over 30,000 seconds on tables with 70 0000 rows in the SC table, 70 000 rows in Student, and 100 rows in Course.

Initial table definitions:

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

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

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

The original query used a sub‑query with IN:

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 was 30,248 s and the EXPLAIN plan showed type=ALL (full table scans) with no index usage.

Adding indexes on the filter columns dramatically improved performance:

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

After indexing, the same query ran in 1.054 s, a >30,000× speed‑up.

Further tuning involved rewriting the query as a join and creating a composite index:

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;

Creating a composite index on (c_id, score) reduced execution time to 0.001 s:

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 article also discusses index merge, multi‑column (left‑most prefix) indexes, covering indexes, and the impact of index selectivity on large tables (e.g., 3 million rows). It shows how MySQL may choose different execution orders (filter‑first vs. join‑first) and how analyzing the EXPLAIN plan helps decide the optimal indexing strategy.

Finally, a concise list of best practices is provided, covering column type choices, single‑column and composite indexes, covering indexes, indexing join and filter columns, avoiding functions in WHERE clauses, and indexing ORDER BY / GROUP BY columns.