Practical Guide to SQL Optimization: Identifying and Improving Slow Queries

During interviews I often ask candidates whether they have encountered slow query problems and how they perform SQL optimization.

Typical answers include generic tips like using indexes, replacing UNION with UNION ALL, avoiding select *, indexing join columns, and steering clear of complex SQL, which are partially correct but lack concrete steps.

Many candidates struggle when asked to actually optimize a complex SQL statement, revealing a gap between theory and practice.

This article presents a practical, end‑to‑end SQL optimization workflow.

Identify Problematic SQL?

Two perspectives are used to judge a SQL statement:

System level : high CPU usage, severe I/O wait, long page response time, timeout errors in application logs.

SQL level : overly long statements, long execution time, full‑table scans, large rows or cost in the execution plan.

An execution plan showing Type=ALL and a huge row count (e.g., 9,950,400) indicates a “bad‑smelling” query.

View Execution Plan?

Use EXPLAIN (or explain sql) to obtain the plan; the article shows a MySQL example.

Key fields in the plan are explained in the following table:

Field

Explanation

id

Identifier of each independent operation; larger values execute earlier.

select_type

Type of each SELECT clause.

table

Name of the object being accessed (usually a table).

partitions

Matched partition info (NULL for non‑partitioned tables).

type

Join operation type.

possible_keys

Indexes that could be used.

key

Index actually used; the best types are const, eq_ref, ref, range, index, and ALL. ALL signals a full table scan.

key_len

Length of the chosen index key in bytes.

ref

Reference object for the current row; NULL if none.

rows

Estimated number of rows scanned.

filtered

Percentage of rows filtered by conditions.

extra

Additional info; warnings like Using filesort or Using temporary indicate potential problems.

After reviewing the plan, you decide on optimization actions and repeat the cycle until the plan looks optimal.

SQL Optimization Case

Slow Query

Table structures:

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_name` varchar(100) DEFAULT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `sales` bigint(20) DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) DEFAULT NULL,
  `order_id` varchar(100) DEFAULT NULL,
  `state` bigint(20) DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Problematic query:

select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
  AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create;

The query joins three tables to fetch orders of user 17 within a 10‑hour window and sorts by creation time.

Optimization Steps

Check data volume of each table (image).

Measure original execution time: 0.21 s (image).

Inspect original execution plan (image).

Identify issues from the plan and SQL:

The user_id column is varchar(50) but the query uses an integer, causing implicit conversion and missing indexes. Convert user_id to int in tables b and c.

Create indexes on user_id for both tables.

Create an index on seller_name for tables a and b.

Use a composite index to eliminate temporary tables and sorting.

Initial optimization statements:

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

After applying these changes, the execution time improves roughly 20‑fold (image).

Further analysis of the new plan reveals warnings about the gmt_create column being a varchar, which prevents the composite index from being used.

Fix the column type:

alter table a modify `gmt_create` datetime DEFAULT NULL;

Re‑measure execution time (image) – performance becomes excellent, and the final execution plan shows no issues.

SQL Optimization Summary

View the execution plan with EXPLAIN.

If warnings appear, run SHOW WARNINGS to investigate.

Examine table structures and existing indexes.

Based on the plan, pinpoint potential bottlenecks.

Apply schema changes, add or modify indexes, and rewrite the SQL as needed.

Check the optimized execution time and plan.

If results are unsatisfactory, repeat from step 4.