MySQL Query Optimization: Indexing, Subqueries, and Join Strategies

The author uses a MySQL 5.6 environment with three tables (Course, Student, and SC) to illustrate common performance problems when querying large datasets.

CREATE TABLE Course (
  c_id INT PRIMARY KEY,
  name VARCHAR(10)
);

CREATE TABLE Student (
  id INT PRIMARY KEY,
  name VARCHAR(10)
);

CREATE TABLE SC (
  sc_id INT PRIMARY KEY,
  s_id INT,
  c_id INT,
  score INT
);

The initial query searches for students whose Chinese score is 100:

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

Running this query on 70 000 SC rows takes over 30 000 seconds because the optimizer performs a full table scan (type=ALL). The first optimization is to add indexes on the columns used in the WHERE clause:

CREATE INDEX sc_c_id_index ON SC(c_id);
CREATE INDEX sc_score_index ON SC(score);

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

Further analysis shows that MySQL rewrites the subquery into an EXISTS form, but the execution plan still scans many rows. Converting the query to an explicit join yields better control:

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;

When the previously created indexes are dropped, the join takes 0.057 seconds, still faster than the original subquery. Adding an index on the foreign key improves it further: CREATE INDEX sc_s_id_index ON SC(s_id); However, with large data volumes the optimizer may still choose suboptimal plans. A composite (multi‑column) index can dramatically reduce the result set size and improve selectivity:

CREATE INDEX sc_c_id_score_index ON SC(c_id, score);

Running the query after adding the composite index reduces execution time to about 0.007 seconds.

The article also covers indexing strategies for a different example table user_test. Single‑column indexes are created on sex, type, and age:

CREATE INDEX user_test_index_sex ON user_test_copy(sex);
CREATE INDEX user_test_index_type ON user_test_copy(type);
CREATE INDEX user_test_index_age ON user_test_copy(age);

For a query filtering on all three columns, MySQL uses an index_merge strategy, but performance is still limited (0.415 seconds). A composite index on the three columns provides a ten‑fold speed‑up:

CREATE INDEX user_test_index_sex_type_age ON user_test(sex, type, age);

Because MySQL can use the leftmost prefix of a composite index, queries that filter on sex alone or on sex and type also benefit from the same index.

Covering indexes are demonstrated by selecting only the indexed columns, eliminating the need to read the table rows:

SELECT sex, type, age
FROM user_test
WHERE sex = 2 AND type = 2 AND age = 10;

This query executes in 0.003 seconds, much faster than selecting all columns.

Finally, the impact of ordering is shown. Adding an index on the user_name column allows MySQL to satisfy an ORDER BY without an extra sort step:

CREATE INDEX user_name_index ON user_test(user_name);

With the index, the ordered query runs in 0.139 seconds instead of a much longer time.

The article concludes with a checklist of best practices: use numeric types, create appropriate single‑column and composite indexes, build covering indexes, index join and filter columns, index sorting and grouping columns, and avoid functions on indexed columns.