SQL Slow Query Optimization Techniques and a Practical Case Study

Many developers encounter slow‑query SQL statements and feel unsure where to start; this article teaches systematic techniques and practical steps to identify and optimize such queries.

Identifying Problematic SQL

Two categories of symptoms can be examined:

System‑level indicators: severe CPU consumption, heavy I/O wait, long page response time, timeout errors in application logs. Tools like sar and top or monitoring platforms such as Prometheus and Grafana help observe these metrics.

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

Acquiring Problematic SQL

Different databases provide distinct mechanisms to capture slow queries:

MySQL – slow query log, LoadRunner, Percona pt‑query tools.

Oracle – AWR reports, internal views (e.g., v$session_wait), GRID CONTROL monitoring.

DM (达梦) – AWR reports, LoadRunner, DM performance monitor, internal views.

SQL Writing Tips

Use indexes wisely: high‑selectivity B‑tree indexes on frequently used WHERE columns, index join columns, consider full‑text indexes for complex document queries, and balance index count with DML overhead.

Prefer UNION ALL over UNION to avoid unnecessary deduplication.

Avoid SELECT * to enable index‑only scans.

Index join fields, keep SQL statements concise, and avoid constructs like WHERE 1=1 or ORDER BY RAND().

SQL Optimization Process

Reading the execution plan is the first step. For MySQL, run EXPLAIN to obtain columns such as id, select_type, table, type, possible_keys, key, key_len, rows, extra, etc. Values like type=ALL or Using filesort indicate potential problems.

Practical Optimization Case

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

Original query requirement (join three tables, fetch orders within ±10 hours of the current time, ordered by creation time):

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;

Initial execution time and plan showed a full‑table scan ( type=ALL) and large row counts.

Optimization Steps

Align data types: change user_id from varchar(50) to int in tables b and c to avoid implicit conversion.

Add indexes on the join columns: user_id in both b and c, and seller_name in a and b.

Create composite indexes to eliminate temporary tables and sorting, e.g., (gmt_create, seller_name, seller_id) on a.

Execute the following DDL 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 the changes, the execution time dropped dramatically and the new plan no longer contained type=ALL or Using filesort.

Further refinement included converting gmt_create to a proper DATETIME column and re‑checking warnings.

Summary of Optimization Workflow

Run EXPLAIN to view the execution plan.

Check any warning messages with SHOW WARNINGS.

Inspect table structures and existing indexes.

Based on the plan, identify bottlenecks (full scans, large row estimates, filesort, temporary tables).

Apply structural changes: modify column types, add or adjust indexes, rewrite SQL.

Re‑measure execution time and review the updated plan.

If performance is still unsatisfactory, repeat the analysis.

By following these steps, most slow‑query problems can be resolved efficiently.