How I Reduced a 30,000‑second MySQL Query to 0.001s with Indexes & Joins

Scenario

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

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

Original Query and Problem

Goal: find students who scored 100 in the Chinese subject (c_id = 0).

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 (over 8 hours). The EXPLAIN plan shows type=ALL for all tables – no indexes are used.

First Optimization – Adding Indexes

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

Running the same query after adding the two indexes drops the time to 1.054 s , a >30,000× improvement, but still far from ideal.

Analyzing the Plan

The plan reveals that MySQL still scans many rows before applying the filter, so further tuning is needed.

Rewrite as EXISTS Sub‑query

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

This version lets MySQL use the indexes efficiently and reduces the runtime dramatically.

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;

After temporarily dropping the previous indexes and creating an index on SC(s_id), the join query runs in 0.057 s . Adding the c_id and score indexes brings it down further.

Best Performing Form – Filter First, Then Join

SELECT s.*
FROM (
  SELECT *
  FROM SC sc
  WHERE sc.c_id = 0 AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id;

With the three indexes ( sc_c_id_index, sc_score_index, sc_s_id_index) this query finishes in 0.001 s , a 50,000× speed‑up over the original.

Key Takeaways

Nested sub‑queries on large tables can be extremely slow if indexes are missing.

Converting the sub‑query to an EXISTS clause or a join often lets MySQL use indexes.

Creating appropriate indexes on the columns used in WHERE and join conditions is essential.

Analyzing the EXPLAIN plan helps identify why a query is not using indexes.

Further optimizations and more examples will be added later.