How to Diagnose and Fix Slow SQL Queries: Practical Steps and Real Examples

Step 1 – Observe the SQL

Complex queries that join many tables or use sub‑queries may run fast on small data sets but become slow as data grows. First understand the business scenario, map table relationships, and try to split the statement into smaller queries that can be combined in memory.

Step 2 – Analyse with EXPLAIN

Run EXPLAIN and focus on the columns type, possible_keys, key, rows and extra. The type column shows the join algorithm; its values from best to worst are system, const, eq_ref, ref, …, ALL. The possible_keys column lists indexes that could be used, while key shows the index actually chosen. Pay attention to Using filesort and Using temporary in extra as signs of inefficiency.

Key tips: the left‑most prefix rule for composite indexes, the meaning of index_merge, unique_subquery, range, index, and ALL scans.

Step 3 – Choose a Fix

If the query cannot be rewritten, design a solution based on data distribution and business needs, such as adding appropriate indexes, forcing an index, or moving sorting to the application layer.

Case Studies

Case 1 – Large result set with filesort

The original query returned ~20 000 rows and sorted by a non‑indexed column oil_gun_price, causing a filesort. Two alternatives were presented: paginate by primary key or fetch all rows without ORDER BY and sort in memory.

SELECT gs.id, gs.gas_code, gs.tpl_gas_code, gs.gas_name, gs.province_id, gs.province_name, gs.city_id, gs.city_name, gs.county_id, gs.county_name, gs.town_id, gs.town_name, gs.detail_address, gs.banner_image, gs.logo_image, gs.longitude, gs.latitude, gs.oil_gun_serials, gs.gas_labels, gs.status, gs.source, gp.oil_number, gp.oil_gun_price FROM fi_club_oil_gas gs LEFT JOIN fi_club_oil_gas_price gp ON gs.gas_code = gp.gas_code WHERE oil_number = 95 AND status = 1 AND gs.yn = 1 AND gp.yn = 1 ORDER BY gp.oil_gun_price ASC;

Case 2 – Sub‑query replaced by join

A sub‑query caused a full table scan and 2.63 s execution. Rewriting it as a join reduced the time to 0.71 s.

SELECT COUNT(0) FROM trans_scheduler_base tsb LEFT JOIN trans_scheduler_calendar tsc ON tsb.scheduler_code = tsc.scheduler_code WHERE tsb.type = 3 AND tsb.yn = 1;

Case 3 – Index mismatch

Even though carrier_code and trader_code have indexes, MySQL chose update_time for ordering, leading to inefficiency. Using FORCE INDEX or changing the ORDER BY clause can resolve it.

SELECT id, carrier_name, carrier_code, trader_name, trader_code, route_type_name, begin_province_name, begin_city_name, begin_county_name, end_province_name, end_city_name, end_county_name FROM carrier_route_config WHERE yn = 1 AND carrier_code = 'C211206007386' AND trader_code = '010K1769496' ORDER BY update_time DESC LIMIT 10;

Case 4 – LIMIT with unsuitable index

A query used create_time index but the WHERE clause did not filter on that column, causing a full scan for LIMIT 10. Reducing LIMIT to the actual row count avoided the scan.

Case 5 – Over‑joined tables

When many tables are joined, consider extracting a base table first and then enriching it, or denormalise into a wide table.

Overall Optimization Principles

Create appropriate indexes

Avoid selecting unnecessary columns

Use covering indexes

Rewrite statements

Data partitioning

Choose suitable columns for sorting

Apply column redundancy where useful

Split complex SQL

Consider using Elasticsearch for heavy read‑only workloads