Why Your SQL Queries Are Slow: 15 Real‑World Causes & Fixes

1. Introduction

Both developers and DBAs encounter slow queries (SELECT, UPDATE, INSERT, DELETE) that affect business stability. "Slow" can mean either noticeably longer than normal (e.g., 10 ms vs. 100 ms) or exceeding a configured threshold such as 500 ms.

One meaning is slower than usual execution time.

The other meaning is execution time exceeding the defined slow‑query standard.

This article analyzes the causes of slow SQL from both architectural and database perspectives.

2. Basic Knowledge

Before analyzing slow queries, we review the SQL execution path to identify factors that may affect speed.

Execution Path

Typical paths:

app —‑> [proxy] —‑> db

app —‑> db

Most database architectures follow one of these paths, and several factors can influence query latency.

<ol><li>Network between nodes</li><li>Operating system (the database server)</li><li>The MySQL database itself</li></ol>

3. System‑Level Factors

3.1 Network Layer

Packet loss and retransmission increase round‑trip time, making the query appear slower from the application side.

Network saturation can occur on high‑traffic days (e.g., double‑11 sales) when the NIC bandwidth is fully utilized, slowing data transmission.

Longer network paths add latency; for example, an extra app‑to‑app hop can add ~3 ms per query, which multiplies quickly with many statements.

3.2 I/O Layer

1. Disk I/O occupied by other tasks

Backup processes that compress data can saturate disk I/O, causing MySQL reads to slow down.

2. RAID cache charge‑discharge or reset

When a RAID controller switches from write‑back to write‑through during battery charge‑discharge, I/O performance drops sharply, leading to slow queries. root@rac1# megacli -FwTermLog dsply -aALL Cache policy changes or controller resets can also cause I/O hangs.

3. I/O scheduling algorithms

noop : FIFO queue, best for flash, RAM, and embedded devices.

deadline : Guarantees service before a deadline, suitable for database workloads.

anticipatory : Inserts a 6 ms delay after the last read to batch writes; good for write‑heavy workloads but poor for databases.

3.3 CPU Type

For database servers, set the CPU power policy to maximum performance mode to avoid throttling.

Reference: Red Hat Enterprise Linux 7 Power Management Guide

4. Database‑Level Factors

4.1 Missing or Incorrect Indexes

Without proper indexes, full table scans occur as data grows, dramatically slowing queries.

4.2 Implicit Type Conversion

When MySQL must perform implicit conversion, it often cannot use an index and falls back to a full scan. Common scenarios include mismatched types in IN clauses or joins.

Example: WHERE a = 1 where column a is a string.

See “聊聊隐式转换”.

4.3 Wrong Execution Plan

The optimizer may choose a sub‑optimal plan, using the wrong index or none at all.

root@rac1 10:48:11> explain select id, gmt_create ... from lol where seller_id=1501204 and service_id=1 and sale_type in (3,4) and use_status in (3,4,5,6) and process_node_id=6 order by id desc limit 0,20 \G

Output shows key: PRIMARY instead of the expected composite index.

4.4 Large Data Sets

Even with an indexed column, a query like SELECT COUNT(*) FROM t1 WHERE app='marketing'; can be slow when the result set contains hundreds of thousands of rows.

SELECT COUNT(*) FROM t1 WHERE app='marketing';

4.5 MetaData Lock (MDL) Waits

Long‑running SELECTs can block DDL statements, causing subsequent writes to wait and increasing thread‑running metrics.

4.6 Concurrent Updates to the Same Row

High‑concurrency scenarios (e.g., flash‑sale updates) cause lock contention and dead‑lock checks, dramatically increasing latency.

4.7 Data Skew

When a column’s value distribution is highly uneven, queries on the high‑cardinality value become much slower.

SELECT COUNT(*) FROM tab WHERE a=1;

SELECT COUNT(*) FROM tab WHERE a=0;

4.8 Inefficient SQL Patterns

Deep pagination such as

SELECT * FROM table WHERE kid=1342 AND type=1 ORDER BY id DESC LIMIT 149420,20;

forces MySQL to scan millions of rows before returning the desired page.

Optimization ideas: Cache results on the front‑end. Use covering indexes to locate rows quickly. Apply bookmark pagination (store the last seen ID).

4.9 Table Schema Design

Field types affect I/O: INT columns are cheap, while large VARCHAR or TEXT columns increase bandwidth and can saturate a 1 Gbps NIC.

For reads, larger VARCHAR values increase response time; for writes, larger rows enlarge binlog traffic.

4.10 InnoDB Dirty‑Page Flushing

InnoDB writes changes to a redo log buffer before flushing to disk. Aggressive or insufficient flushing can cause memory pressure or I/O saturation, leading to sudden query slowdowns.

"With heavy DML activity, flushing can fall behind or saturate I/O, causing normally fast SQL to become slow."

4.11 Undo Log Not Purged

UNDO records keep historical row versions. Long‑running or uncommitted transactions prevent undo reclamation, forcing queries to traverse many undo entries and slowing execution.

Each row points to its latest UNDO record; chains of UNDO entries must be traversed to reconstruct older versions.

5. Summary

The article lists fifteen practical scenarios that can degrade MySQL query performance, ranging from hardware and OS issues to schema design and optimizer quirks. Readers are encouraged to share additional cases they have encountered.