SQL Optimization Techniques: Indexing, Subqueries, and Join Strategies in MySQL

The author presents a MySQL 5.6 performance scenario where three tables (Course, Student, SC) contain 100, 70,000 and 700,000 rows respectively, and the goal is to find students who scored 100 in a specific course.

Initial query (slow):

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
)

The execution time was 30,248 seconds and the EXPLAIN plan showed type=ALL (full table scans) with no index usage.

First optimization: create indexes on the filter columns.

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

After adding these indexes the same query ran in 1.054 seconds – a >30,000× speed‑up.

The author then examines why the query still takes a second and looks at the revised EXPLAIN plan, noting that MySQL rewrites the sub‑query into an EXISTS form.

Alternative rewrite using EXISTS:

SELECT `YSB`.`s`.`s_id` AS `s_id`, `YSB`.`s`.`name` AS `name`
FROM `YSB`.`Student` `s`
WHERE
(`YSB`.`s`.`s_id`,
(
    SELECT 1 FROM `YSB`.`SC` `sc`
    WHERE (`YSB`.`sc`.`c_id` = 0) AND (`YSB`.`sc`.`score` = 100)
    AND
(`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`)
);

Running the EXISTS version also finishes in about 1 second.

Join rewrite:

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;

With the previously created indexes removed, the join took 0.057 seconds; after re‑adding the indexes it took 1.076 seconds, illustrating that the join order and index choice affect performance.

Adding an index on SC(s_id) did not improve the join because the optimizer still chose a less efficient plan.

Composite index experiment: the author drops the single‑column indexes and creates a composite index on (c_id, score) . 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 seconds, confirming that a multi‑column index can be far more selective. Further tests on a different table user_test_copy (300 M rows) compare single‑column indexes, index‑merge, and a composite index on (sex, type, age) . The composite index reduces execution time from 0.415 s to 0.032 s (≈10× faster). The article also covers covering indexes (selecting only indexed columns) which cut query time to 0.003 s, and sorting optimization by indexing the ORDER BY column. Key take‑aways (summarised in the original list): MySQL nested sub‑queries can be very slow; rewrite them as joins or EXISTS. Build appropriate single‑column indexes on filter fields. When individual columns have low selectivity, create multi‑column (composite) indexes. Use covering indexes to avoid fetching full rows. Index join columns and ORDER BY columns to speed up joins and sorting. Avoid functions on WHERE columns to keep indexes usable. Overall, the article demonstrates a systematic approach to diagnosing slow SQL, interpreting EXPLAIN output, and applying indexing strategies to achieve dramatic performance improvements.