SQL Optimization: From 30,248s to 0.001s

Scenario

Database: MySQL 5.6. Three tables: Course (100 rows), Student (70,000 rows), SC (700,000 rows). Goal: find students who scored 100 in Chinese (c_id=0). Initial query uses a subquery with IN clause.

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 seconds.

Initial Execution Plan Analysis

EXPLAIN shows type=ALL for both tables, no indexes used. First optimization: create single-column indexes on SC.c_id and SC.score.

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

After indexing, query time drops to 1.054 seconds (30,000x improvement). However, 1 second still too slow.

Understanding MySQL's Subquery Transformation

Examining the optimized query via EXPLAIN EXTENDED + SHOW WARNINGS reveals MySQL rewrites the IN subquery as an EXISTS correlated subquery with a DEPENDENT SUBQUERY. This causes the outer query to execute first, looping 70,007 times and executing the inner query each time.

Running the inner query alone takes 0.001s (returns 3 rows). Running the outer query with those IDs also takes 0.001s. But the combined query takes 1s due to the dependent subquery execution pattern.

Join Query Optimization

Rewrite as INNER JOIN:

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 on SC.s_id, time is 0.057s. Execution plan shows WHERE filter applied before join (good). Adding index on SC.s_id surprisingly increases time to 1.076s because MySQL changes plan to join first then filter.

Forcing Filter-First with Derived Table

To ensure filter-first, use a derived table:

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

Time: 0.054s (similar to join without s_id index). Execution plan shows derived table scanned (type=ALL). Adding back indexes on c_id and score reduces time to 0.001s. Now both derived table and join use indexes.

Interestingly, the original join query now also runs in 0.001s because MySQL's optimizer chooses the filter-first plan with indexes present.

Regression with Larger Data and Composite Index

Later, SC table grows to 3 million rows, scores more dispersed. Query with c_id=81 and score=84 takes 0.061s. Execution plan shows index_merge (intersect) on two single-column indexes. Single-column selectivity: c_id=81 returns 70,001 rows; score=84 returns 39,425 rows; combined returns 897 rows. Combined selectivity is high, so composite index on (c_id, score) is created.

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

Query time drops to 0.007s. Execution plan shows ref access using composite index.

Index Optimization Principles

Single-Column vs Composite Indexes

Test on user_test_copy (3M rows) with three single-column indexes on sex, type, age. Query with three equality conditions uses index_merge (intersect) taking 0.415s. Creating composite index on (sex, type, age) reduces time to 0.032s (10x faster). Composite index also obeys leftmost prefix: queries with sex alone, sex+type, sex+age use index; but type alone or age alone would not.

Covering Index

Selecting only indexed columns (sex, type, age) avoids table lookup: time 0.003s vs 0.032s for SELECT *.

Sorting Optimization

ORDER BY user_name on filtered result takes 0.139s. Adding index on user_name improves sort performance.

Summary of SQL Tuning Best Practices

MySQL nested subqueries are inefficient; rewrite as joins.

For joins, filter tables with WHERE before joining (though MySQL may optimize automatically).

Create appropriate indexes; use composite indexes when single-column selectivity is low.

Analyze execution plans; MySQL rewrites queries, so verify actual plan.

Prefer numeric types for columns, keep length short.

Create single-column indexes where needed.

Create composite indexes based on business needs, especially when single-column filtering leaves many rows.

Use covering indexes for queries that only need indexed columns.

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

Avoid functions on indexed columns in WHERE to prevent index invalidation.