Master Real-World SQL Optimization: From Identifying Slow Queries to 20× Speed Boost

Identifying Problematic SQL

In practice you can judge a query is problematic from system‑level symptoms (high CPU, IO wait, slow page response, timeout logs) and from the SQL itself (excessive length, long execution time, full‑table scans, large rows or cost in the plan).

Inspecting the Execution Plan

Running EXPLAIN SELECT … in MySQL returns a plan where the type column indicates the join method. A value of ALL with a huge rows count (e.g., 9,950,400) signals a “bad‑smelling” query that scans the whole table.

The plan also contains fields such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered and extra. The extra column shows warnings like Using filesort or Using temporary, which indicate further optimisation opportunities.

Case Study: Slow Query

Schema

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 SQL

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 orders the result by creation time.

Initial Optimisation Steps

Check data volume of each table (image).

Measure original execution time (≈0.21 s, image).

Inspect the original execution plan (image).

Identify issues:

Type mismatch: user_id is varchar(50) but the query uses an integer, causing implicit conversion and preventing index use.

Missing indexes on user_id in tables b and c.

Missing index on seller_name for the join between a and b.

Potential to eliminate temporary tables and sorting with a composite index.

First Set of ALTER 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 twenty‑fold.

Further Optimisation

Running SHOW WARNINGS reveals:

Cannot use range access on index ‘idx_sellname_gmt_sellid’ due to type or collation conversion on field ‘gmt_create’

The gmt_create column is stored as varchar, forcing a type conversion during the BETWEEN filter, which prevents index usage. Convert it to datetime:

ALTER TABLE a MODIFY `gmt_create` datetime DEFAULT NULL;

After the conversion the query runs with an optimal plan and execution time becomes negligible.

SQL Optimisation Checklist

Run EXPLAIN to view the execution plan.

If extra shows warnings, run SHOW WARNINGS.

Examine table definitions and existing indexes.

Based on the plan, decide which columns need type fixes, new indexes, or query rewrites.

Apply ALTER TABLE statements, add indexes, or refactor the SQL.

Re‑measure execution time and review the new plan.

If performance is still unsatisfactory, repeat from step 4.