How to Speed Up MySQL Queries: Indexing and Execution Plan Optimization

Scenario

Three tables are created in MySQL 5.6: Course (100 rows), Student (70,000 rows) and SC (700,000 rows). The task is to find students who scored 100 in the Chinese subject (c_id = 0).

Initial Query and Performance

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

The query runs for 30248.271 seconds because MySQL performs a full table scan (type = ALL) and does not use any index. The execution plan image shows no index usage.

Adding Indexes

Indexes are created on the columns used in the WHERE clause of the subquery.

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

After adding these indexes the same query finishes in 1.054 seconds , a speed‑up of more than 30,000×.

Further Optimization – Join Rewrite

MySQL rewrites the subquery to an EXISTS form, which still scans many rows. Rewriting the query as an explicit join can be faster, but first the previously created indexes are dropped.

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;

An index on SC(s_id) is added: CREATE index sc_s_id_index on SC(s_id); The join executes in 0.057 seconds , but a later run shows a slower time (1.076 s) because the optimizer chooses a different plan.

Composite Index

To force the optimizer to use the most selective columns, the single‑column indexes are dropped and a 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);

With the composite index the query runs in 0.007 seconds , confirming the benefit of multi‑column indexes when individual column selectivity is low.

Additional Example – Multi‑Column Index on a Different Table

Three single‑column indexes are created on user_test_copy(sex), user_test_copy(type) and user_test_copy(age). A query filtering on all three columns takes 0.415 s and uses the index_merge strategy.

Creating a composite index on the three columns dramatically improves performance.

CREATE index user_test_index_sex_type_age on user_test(sex, type, age);

The same query now finishes in 0.032 s , more than ten times faster.

Best‑Practice Summary

Nested subqueries on large tables are usually slow; rewrite them as joins when possible.

Filter rows with WHERE conditions before joining to reduce the amount of data processed.

Create appropriate single‑column indexes on columns used in WHERE, JOIN, ORDER BY and GROUP BY clauses.

When single‑column selectivity is low, build multi‑column (composite) indexes; they follow the left‑most prefix rule.

Use covering indexes (include all selected columns) to avoid extra row lookups.

Avoid applying functions or calculations to indexed columns in WHERE clauses, as this disables index usage.

Index the columns involved in sorting to speed up ORDER BY operations.

References

http://www.cnblogs.com/linfangshuhellowored/p/4430293.html

http://tech.meituan.com/mysql-index.html

http://www.cnblogs.com/Toolo/p/3634563.html

http://www.cnblogs.com/mliang/p/3637937.html

http://www.cnblogs.com/xwdreamer/archive/2012/07/19/2599494.html

http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html