Why Your SQL Queries Are Slow: 10 Hidden Factors and How to Fix Them

1. Introduction

Both developers and DBAs often encounter slow queries (SELECT, UPDATE, INSERT, DELETE) that affect business stability. "Slow" can mean a query that normally takes 10 ms now takes 100 ms, or any query exceeding a configured threshold such as 500 ms.

This article analyzes the causes of slow SQL execution from both IT architecture and database perspectives, inviting readers to suggest improvements.

2. Fundamentals

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

Execution Path

app —[proxy]— db

app — db

Most database architectures follow these paths: the application server sends SQL to the database through a proxy or directly, and the database returns results. From this process we can derive several factors influencing execution speed. 1 Network between nodes 2 Operating system (the database server) 3 MySQL database itself

3. System Layer Fundamentals

3.1 Network Layer

Network packet loss and retransmission – When SQL results travel from the database back to the app, unstable links cause packet loss, leading to retransmissions and increased latency, perceived as slower SQL execution.

Image source: cloud.tencent.com

Network interface saturation – High‑traffic e‑commerce platforms can saturate NIC bandwidth during peak events, causing data transfer delays.

Long network links – Additional hops add latency (e.g., 15 km intra‑datacenter link ≈ 1.5 ms). A query traversing two extra hops can add ~3 ms per request; 30 queries in a transaction may add ≥ 90 ms.

3.2 I/O Impact Scenarios

1. Disk I/O occupied by other tasks

Backup processes using compression (e.g., xtrabackup) can consume CPU and I/O, causing MySQL to experience high disk I/O and resulting in many slow queries.

2. RAID controller battery charge/discharge

When the battery‑backed write cache is low, the controller switches from write‑back to write‑through mode, degrading I/O performance and increasing thread load.

root@rac1
# megacli -FwTermLog dsply -aALL
...

RAID cache change – Switching cache policies can dramatically slow I/O, leading to slow SQL.

3.3 CPU Considerations

For database servers, set the CPU power policy to maximum performance . (Reference: Red Hat Enterprise Linux 7 Power Management Guide)

https://access.redhat.com/documentation/zh-cn/red_hat_enterprise_linux/7/pdf/power_management_guide/Red_Hat_Enterprise_Linux-7-Power_Management_Guide-zh-CN.pdf

2. Instruction set – Disabled CPU instruction sets can cause a 15 % performance drop.

https://support.huawei.com/enterprise/zh/doc/EDOC1000039566/c2662e35

4. Database Layer

4.1 Missing or Incorrect Indexes

Without proper indexes, tables are scanned fully, causing queries to slow as data grows.

4.2 Implicit Type Conversion

Implicit conversions prevent MySQL from using suitable indexes, leading to full table scans. Examples include mismatched types in IN clauses or joins.

4.3 Wrong Execution Plans

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

root@rac1
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

Result shows type: index and key: PRIMARY instead of the expected composite index.

4.4 Large Data Volumes

Even with indexes, queries like select count(*) from t1 where a='xxxx' can be slow when matching rows exceed 100 k.

select count(*) from t1 where a='marketing';
+----------+
| count(*) |
+----------+
| 2671690 |
+----------+

4.5 Metadata Lock (MDL) Waits

Long‑running queries or mysqldump can block DDL, causing subsequent writes to wait and increasing thread‑running counts.

4.6 Concurrent Updates to the Same Row

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

4.7 Skewed Data Distribution

When a column’s value distribution is heavily imbalanced, queries on the high‑cardinality value run much slower.

select count(*) from tab where a=1 ;

4.8 Poor SQL Patterns

Deep pagination ( LIMIT N,M) forces MySQL to scan N rows before returning M rows, leading to high I/O cost.

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

Optimization suggestions include caching, using covering indexes, or bookmark pagination.

4.9 Table Structure Design

Field types affect performance: larger VARCHAR or TEXT columns increase bandwidth usage and write latency.

For reads, int columns perform similarly, but larger varchar/text columns increase response time.

For writes, larger fields enlarge binlog size, consuming more network bandwidth.

4.10 InnoDB Dirty Page Flushing

InnoDB uses Write‑Ahead Logging; buffers are flushed to disk periodically. Aggressive flushing can saturate I/O, while insufficient flushing leads to memory pressure, both causing sudden query slowdowns.

With heavy DML activity, flushing can fall behind if it is not aggressive enough, resulting in excessive memory use in the buffer pool; or, disk writes due to flushing can saturate your I/O capacity if that mechanism is too aggressive.

4.11 Undo Log Not Purged

UNDO logs store previous row versions for MVCC. Long‑running or uncommitted transactions prevent undo cleanup, increasing the amount of undo data scanned and slowing queries.

5. Summary

This article lists several common scenarios that can degrade SQL performance, ranging from network and hardware issues to MySQL configuration, indexing, execution plans, and query patterns. Readers are encouraged to share additional cases they have encountered.