How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive

Scenario

The database is MySQL 5.6 with three tables: Course (100 rows), Student (70,000 rows), and SC (700,000 rows). The goal is to find students who scored 100 in the Chinese subject (c_id = 0).

Initial Query and Problem

The original query uses a sub‑query with IN and runs in 30,248 seconds. An EXPLAIN shows a full table scan (type=ALL) with no index usage.

Creating indexes on SC(c_id) and SC(score) reduces execution time to 1.054 seconds, a >30,000× improvement.

Further Optimization Attempts

Even after indexing, the query still takes about 1 second. Analyzing the plan reveals that MySQL rewrites the sub‑query as an EXISTS clause with dependent sub‑queries.

Switching to an inner join without indexes yields 0.057 seconds, but adding an index on SC(s_id) unexpectedly increases time to 1.076 seconds due to a less efficient plan.

Re‑writing the query to first filter SC and then join to Student brings the time down to 0.001 seconds.

Scaling Issue and Composite Index

When SC grows to 3 million rows, the previous indexes become slower (0.061 seconds). Because the selectivity of c_id and score together is high, a composite index SC(c_id,score) reduces execution time to 0.007 seconds.

Summary of Findings

Nested sub‑queries in MySQL can be very slow.

Converting them to joins often improves performance.

Filtering tables before joining (using WHERE) can be more efficient.

Appropriate single‑column and multi‑column indexes are essential.

Analyzing execution plans is crucial because MySQL may rewrite queries.

Index Optimization Details

Single‑column indexes on sex, type, and age for a 3‑million‑row table result in 0.415 seconds with an index_merge plan.

A composite index user_test(sex,type,age) reduces the same query to 0.032 seconds.

The left‑most prefix rule applies: the first indexed column must appear in the WHERE clause for the index to be used.

Covering indexes (where all selected columns are indexed) further speed up queries, e.g., selecting only sex, type, age runs in 0.003 seconds.

Adding an index on the ORDER BY column ( user_name) improves sorting performance.

Practical Tips

Prefer numeric column types with minimal length for keys.

Create single‑column indexes on frequently filtered fields.

Use composite indexes when multiple columns together provide high selectivity.

Leverage covering indexes to avoid extra row lookups.

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

Avoid functions on indexed columns in WHERE clauses.