MySQL Query Optimization: Indexing Strategies and Execution Plan Analysis

Scenario

The example uses MySQL 5.6 with three tables: Course (100 rows), Student (70,000 rows), and SC (700,000 rows) that stores student scores for courses.

Table definitions

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

Query purpose

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

Execution time: 30248.271 s . The EXPLAIN plan shows type=ALL (full table scan) because no indexes are used.

Adding single‑column indexes

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 runs in 1.054 s , a >30,000× speed‑up.

Further optimization with EXISTS

SELECT `YSB`.`s`.`s_id` AS `s_id`,
       `YSB`.`s`.`name` AS `name`
FROM `YSB`.`Student` `s`
WHERE < in_optimizer > (
  `s`.`s_id`, EXISTS (
    SELECT FROM `YSB`.`SC` `sc`
    WHERE (`sc`.`c_id` = 0) AND (`sc`.`score` = 100)
      AND (< CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`)
  )
);

This version finishes in 0.001 s because MySQL rewrites the subquery to an EXISTS check that can use the indexes.

Join‑based rewrite

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;

Without indexes the join takes 0.057 s . Adding an index on SC(s_id) makes the join slower ( 1.076 s ) because the optimizer still scans many rows.

Composite index

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

The same query now runs in 0.007 s , confirming that a multi‑column index on the filtered columns gives the best performance.

Single‑column vs multi‑column indexes (user_test example)

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

Query on three columns with only single‑column indexes takes 0.415 s and uses type=index_merge (MySQL merges the three indexes).

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

The same query with the composite index finishes in 0.032 s , a ten‑fold improvement. The index works because of the left‑most prefix rule (the first column sex appears in the WHERE clause).

Index coverage

select sex, type, age from user_test
where sex = 2 and type = 2 and age = 10;

Because all selected columns are covered by the composite index, the query runs in 0.003 s without accessing the table data.

Sorting optimization

select * from user_test where sex = 2 and type = 2 order by user_name;

Adding an index on user_name reduces the sorting time from 0.139 s to a much lower value.

create index user_name_index on user_test(user_name);

Summary

Nested subqueries in MySQL can be very slow; rewrite them as EXISTS or JOINs.

Apply appropriate indexes on the columns used in WHERE and JOIN conditions.

When single‑column indexes give low selectivity, create a multi‑column (composite) index to increase discrimination.

Use index coverage (select only indexed columns) to avoid table lookups.

Index the columns used for ORDER BY, GROUP BY, and foreign‑key joins to speed up sorting and joining.

Analyze EXPLAIN output to understand how MySQL executes a query and to guide index design.