How Indexes Can Slash MySQL Query Time from Hours to Milliseconds

Scenario

The database uses MySQL 5.6 with three tables: a student table (100 rows), a score table SC (70,000 rows), and a student‑score table (700,000 rows). The goal is to find students who scored 100 in Chinese.

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 . The execution plan shows type=ALL (full table scan) and no index usage.

First optimization – add indexes on the WHERE columns

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× speedup.

Further analysis

Even 1 s is long for such a query. The execution plan reveals that MySQL first performs a join then filters, causing many row combinations. By rewriting the query to let MySQL filter before joining, performance improves.

Join vs subquery

MySQL can transform the subquery into an EXISTS clause, but still may execute the outer query repeatedly (≈70 000 × 8 loops). Switching to an explicit join and ensuring proper indexes reduces the time to 0.057 s .

Index strategy

Single‑column indexes improve filtering on individual columns.

When data volume grows (e.g., SC table to 3 million rows) and column selectivity is low, single indexes become less effective.

Creating a composite (multi‑column) index on (c_id, score) dramatically reduces execution time to 0.007 s .

MySQL can merge multiple single‑column indexes (INTERSECT) but a composite index is usually faster.

Composite index benefits

Leverages the left‑most prefix rule: the first indexed column must appear in the WHERE clause.

Enables covering indexes when all selected columns are indexed, allowing MySQL to return results directly from the index without accessing the table data.

Additional optimizations

Define numeric columns with the smallest appropriate type.

Build indexes on columns used in JOIN, WHERE, ORDER BY, and GROUP BY clauses.

Avoid functions on indexed columns in WHERE conditions to prevent index loss.

Summary

Nested subqueries in MySQL can be very slow.

Transform them into JOINs when possible.

Filter rows before joining to reduce intermediate result size.

Create appropriate single‑column and multi‑column indexes; composite indexes often give the best performance.

Analyze execution plans regularly; MySQL may rewrite queries, so understanding the plan is crucial.