Why Slow SQL Hurts Your MySQL and How to Fix It

1. Harm of Slow SQL

Slow SQL statements consume valuable CPU, I/O, and memory resources, especially under high concurrency, causing request blocking, timeouts, and large‑scale failures. An everyday analogy compares slow queries to passengers occupying a restroom for too long, preventing others from using the resource.

2. SQL Execution Process

The client sends a statement to the MySQL server, which validates credentials, checks the query cache, parses the SQL, runs the optimizer to produce a physical execution plan, invokes the storage engine, retrieves data, and finally returns the result to the client.

The optimizer’s role is to choose the most efficient execution path among many possibilities.

3. Storage Engine and Indexes

InnoDB is the default MySQL storage engine. It separates memory and disk architectures, stores rows in 16 KB pages, and uses a B+‑tree for both clustered (primary) and secondary indexes.

Clustered index (primary key) stores row data together with the index; lookup typically requires 1–3 disk I/Os because the root node resides in memory.

Secondary index stores only the indexed column and the primary key. Queries using a secondary index need an extra “lookup” (back‑table) step to fetch the full row, which adds another I/O.

Key storage concepts include:

Disk pre‑read and locality reduce I/O by loading adjacent pages.

Random I/O is expensive because data pages are not physically contiguous.

Sector (512 B), block (≈4 KB), and page (multiple of block, default 16 KB) sizes affect performance.

4. Solving Slow SQL

Two main causes are poor index design and inefficient SQL statements.

4.1 Index Design Principles

Avoid low‑selectivity indexes (e.g., a column where 90 % of rows have the same value).

Do not create excessive indexes; each index consumes disk space and slows DML operations.

Index frequently queried columns, columns used for ORDER BY/GROUP BY/DISTINCT, and primary/foreign keys.

Prefer composite (covering) indexes over multiple single‑column indexes.

4.2 SQL Optimization Techniques

Avoid functions, arithmetic, or implicit conversions on indexed columns (e.g., SELECT id FROM std WHERE UPPER(name)='JIM'; or SELECT id FROM std WHERE id+1=10;).

Do not use leading wildcards in LIKE patterns ( LIKE '%jim').

Write queries that can use indexes fully; avoid OR conditions that involve non‑indexed columns.

Follow the left‑most rule for composite indexes; the query must reference the leading columns.

Use JOINs instead of subqueries, limit large OFFSETs, and employ covering indexes to avoid back‑table lookups.

When joining tables, place the smaller table first so its scan is cheaper.

Order WHERE conditions from most selective to least to reduce intermediate result sets.

4.3 Slow‑Query Analysis Setup

Enable the slow‑query log: SET GLOBAL slow_query_log = 1; Set a threshold (e.g., 3 seconds): SET GLOBAL long_query_time = 3; Use EXPLAIN to view the execution plan:

EXPLAIN SELECT * FROM std WHERE id < 100;

5. Conclusion

The article covered the impact of slow SQL, the InnoDB storage engine, clustered and secondary indexes, common reasons for index loss, and a collection of practical optimization tips. Mastering these concepts helps reduce query latency and improves overall MySQL reliability.