Why MySQL Queries Can Be 50,000× Faster with Proper Indexing

Scenario

The database is MySQL 5.6 with three tables: Course (100 rows), Student (70,000 rows), and SC (700,000 rows) storing student scores.

Initial Query and Problem

The goal is to find students who scored 100 in the Chinese course (c_id = 0). The original query uses a sub‑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 EXPLAIN plan shows type=ALL (full table scan) and no index usage.

Adding Single‑Column Indexes

Indexes are created on the filter columns of SC:

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

After rebuilding the same query, execution time drops to 1.054 s (over 30,000× faster).

Further Optimization: Join vs Sub‑Query

MySQL rewrites the sub‑query to an EXISTS form, but the optimizer still executes the outer query first, causing many row‑wise checks. Rewriting as an explicit join improves performance:

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 previous indexes removed, this join runs in 0.057 s . Adding an index on SC(s_id) further reduces time, but the plan shows the join still scans many rows.

Composite (Multi‑Column) Index

Because the filter uses both c_id and score, a composite index provides higher selectivity:

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

Running the original query with this composite index yields an execution time of 0.007 s .

Additional Indexing Tips

Single‑column indexes on frequently filtered fields (e.g., sex, type, age) improve query speed but may trigger type=index_merge when multiple indexes are combined.

Multi‑column indexes follow the left‑most prefix rule; the first column must appear in the WHERE clause to be used.

Covering indexes (all selected columns indexed) avoid reading the table data entirely, further speeding up queries.

Indexing columns used in ORDER BY or GROUP BY reduces sorting overhead.

Conclusion

Nested sub‑queries in MySQL can be extremely slow.

Rewriting them as joins or letting MySQL transform them to EXISTS can improve performance.

Applying appropriate single‑column and composite indexes, especially on filter and join columns, is essential.

Analyzing the EXPLAIN plan is crucial because MySQL may reorder operations.

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