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

Slow SQL is a major risk for service stability, especially during large promotions. This article explains how to detect, analyze, and resolve slow queries using practical steps and real‑world examples.

Step 1: Observe the SQL

Complex queries with many joins, functions, or sub‑queries may run fast on small data but become slow as data grows. Understand the business scenario and try to split the query into simpler parts that can be combined in memory.

Step 2: Analyze the problem

Use the

EXPLAIN

statement to view the execution plan. Key columns to examine are type , possible_keys , key , rows , and extra . The

type

values, from best to worst, are:

system

const

eq_ref

ref

index_merge

unique_subquery

index_subquery

range

index

ALL

Other important fields are possible_keys (indexes that could be used), key (the index actually chosen), and extra (e.g.,

Using filesort

,

Using temporary

).

Step 3: Specify the solution

If the query cannot be improved directly, design a new plan based on data distribution and business needs.

Case Studies

Case 1 : A query returning ~20 000 rows and sorting by a non‑indexed column caused a filesort. Solution: paginate by primary key or sort in application memory.

<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;</code>

Case 2 : A sub‑query caused a full‑table scan (2.63 s). Rewriting it as a join reduced execution time to 0.71 s.

<code>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;</code>

Case 3 : An index on

update_time

was chosen unexpectedly, leading to poor performance. Using

FORCE INDEX

or changing the order clause solved the issue.

<code>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;</code>

Additional examples illustrate how

LIMIT

with inappropriate ordering can trigger full scans, how excessive joins increase data volume, and how hot‑data concentration affects query speed.

Overall Optimization Principles

Create appropriate indexes

Avoid selecting unnecessary columns

Use covering indexes

Rewrite statements

Data archiving

Choose suitable columns for sorting

Apply column redundancy when needed

Split large SQLs

Consider using Elasticsearch for heavy search workloads