SQL Optimization: Cutting Query Time from 30,248 s to 0.001 s

Scenario: Using MySQL 5.6, three tables are created – Course (100 rows), Student (70,000 rows) and SC (700,000 rows). The goal is to find students who scored 100 in the Chinese course (c_id = 0).

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

Execution time: 30248.271 s . EXPLAIN shows type=ALL for all tables, meaning no index is used.

First optimization: create indexes on the filter columns 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× speed‑up, but still far from ideal. Further analysis reveals MySQL rewrites the sub‑query to an EXISTS form with a dependent sub‑query. Optimized query:

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 sub‑query alone takes 0.001 s , and the outer query also 0.001 s , confirming the dramatic improvement. Observation: MySQL executes the outer query first, then the inner sub‑query, causing many loops (≈70,007 × 8). To avoid this, a join is tried.

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;

Without indexes the join runs in 0.057 s . Adding an index on SC(s_id) unexpectedly slows it to 1.076 s because the plan still scans many rows (type=ALL). Composite index test: drop the previous single‑column indexes and create a multi‑column 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);

Execution time improves to 0.007 s . The plan shows both tables using indexes. Further composite‑index query:

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: 0.001 s , confirming that filtering SC first and then joining is optimal. Additional experiments on a different table user_test_copy (300 w rows) illustrate single‑column and multi‑column indexing:

Single‑column indexes on sex, type, age yield 0.415 s with type=index_merge (MySQL’s intersect of multiple indexes).

Creating a composite index (sex,type,age) reduces time to 0.032 s (over 10× faster). The left‑most prefix rule is demonstrated: queries that filter on the first column(s) of the index can use it.

Covering index example selecting only indexed columns ( sex, type, age) runs in 0.003 s , showing the benefit of index‑only scans.

Adding an index on the ORDER BY column ( user_name) cuts sorting time from 0.139 s to a negligible amount.

Key take‑aways:

Nested sub‑queries in MySQL can be very slow; rewrite them as EXISTS or JOIN when possible.

Filter tables with WHERE conditions before joining to reduce row‑count processed.

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

Use covering indexes to avoid fetching full rows from disk.

Analyze EXPLAIN output; MySQL may reorder operations, so understanding the plan is essential.

Remember the left‑most prefix rule for composite indexes and add indexes on columns used for sorting or grouping.