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

Why Slow SQL Is a Hidden Threat

During major sales events, slow SQL statements can severely disrupt service stability, often causing the entire application to jitter. Complex queries that once ran fast on small data sets become bottlenecks as data grows, so developers must know how to detect and resolve them.

Step 1 – Observe the SQL

Identify queries that involve many tables, sub‑queries, or heavy functions. Such statements may have been acceptable early in a project but turn into performance problems as business volume increases. Understanding the business scenario and breaking the query into smaller parts can help isolate the issue.

Step 2 – Analyze with EXPLAIN

Run EXPLAIN on the query and focus on the columns type , possible_keys , key , rows , and extra . The type column shows the join method; its values from best to worst are:

system : a one‑row system table (special case of const)

const : primary‑key or unique‑index equality scan, returns at most one row

eq_ref : uses all parts of a PRIMARY or UNIQUE NOT NULL index

ref : uses left‑most prefix of a non‑unique index

Additional types you may encounter include fulltext , ref_or_null , index_merge , unique_subquery , index_subquery , range , index , and ALL . Pay special attention to Using filesort and Using temporary in the extra column, as they indicate costly operations.

Step 3 – Propose a Fix

If the query cannot be optimized directly, design a solution based on business needs and data distribution. Common approaches include rewriting the query, adding or adjusting indexes, using covering indexes, or moving heavy filtering to an external system such as Elasticsearch.

Case Studies

Case 1 – Large Result Set with Filesort

The query returns ~20 000 rows and sorts by a non‑indexed column oil_gun_price, causing a filesort. Two solutions are presented:

Convert to a paginated query ordered by id and perform in‑memory sorting after fetching a reasonable page.

Fetch all matching rows without ordering, then sort in application code.

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 Causing Full Scan

A sub‑query loads an entire table before joining, leading to a 2.63 s execution time. Rewriting it as a direct join reduces the time to 0.71 s.

-- Original (slow)
SELECT COUNT(0)
FROM trans_scheduler_base tsb
INNER JOIN (
    SELECT scheduler_code, vehicle_number, vehicle_type_code
    FROM trans_scheduler_calendar
    WHERE yn = 1
    GROUP BY scheduler_code
) tsc ON tsb.scheduler_code = tsc.scheduler_code
WHERE tsb.type = 3 AND tsb.yn = 1;

-- Optimized
SELECT COUNT(DISTINCT tsc.scheduler_code)
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 AND tsc.yn = 1;

Case 3 – Mis‑chosen Index

Even though carrier_code and trader_code have indexes, MySQL picks update_time for sorting, which may be sub‑optimal. Using FORCE INDEX or changing the order clause can improve performance.

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;

Additional Observations

Using LIMIT with ORDER BY can force a full scan if the optimizer cannot use an ordered index.

When the requested limit exceeds the actual row count, MySQL may still scan the whole table; adjusting the limit to the expected row count can avoid unnecessary work.

Excessive table joins increase memory usage; consider denormalizing or building a wide table for read‑heavy scenarios.

Hot‑spot data (e.g., recent timestamps) can cause large scans; add selective predicates to narrow the range.

Counting total rows for pagination can be expensive; separate the count query, cache results, or use an external store like Elasticsearch.

Overall Optimization Principles

Create appropriate indexes.

Read only necessary columns.

Prefer covering indexes.

Rewrite inefficient statements.

Perform data archiving or partitioning.

Choose suitable columns for sorting.

Introduce column redundancy when beneficial.

Split complex SQL into simpler parts.

Apply Elasticsearch or other search engines when relational queries become a bottleneck.

By following these steps and principles, developers can systematically locate and resolve slow SQL issues, ensuring stable performance during high‑traffic periods.