How to Diagnose and Optimize Slow MySQL Queries for Large‑Scale Systems

As a recent backend graduate who landed a job at a top tech company, I share my experience on keeping MySQL performance strong when handling millions of rows.

Why a SQL can be slow

Two situations are considered: occasional slowness, which may be caused by dirty‑page flushing, lock contention, or poorly written SQL; and consistently slow execution, often due to missing or ineffective indexes and full‑table scans.

How to enable MySQL slow query logging

Method 1: add the following lines to my.ini (set slow_query_log=ON and long_query_time=2 seconds).

Method 2: enable the slow query log at runtime with MySQL commands.

Analyzing slow query logs

Use show processlist; to locate inefficient queries and EXPLAIN to view the execution plan.

Key fields in EXPLAIN output:

type : access method (ALL = full scan, index = index scan, range = range scan, eq_ref = unique index, NULL = no table access).

possible_keys : indexes that could be used.

key : index actually used.

key_len : length of the used index.

rows : number of rows examined.

Extra : additional info such as using index (covering index), using where (needs row lookup), using filesort (extra sorting).

Index optimization

Prefer covering indexes; MySQL 5.6 supports index push‑down.

Follow the left‑most rule for composite indexes.

Avoid index loss; in read‑heavy scenarios use normal indexes instead of unique indexes to benefit from change‑buffer optimization.

Build indexes on WHERE and ORDER BY columns with high cardinality; avoid over‑indexing; add indexes to foreign keys.

SQL statement optimization

Pagination: replace LIMIT offset, count with SELECT * FROM tb_sku WHERE id>20000 LIMIT 10; when the primary key is auto‑increment.

Batch INSERTs into a single statement, wrap inserts in a transaction, and insert rows in primary‑key order.

Schema optimization

Split wide tables into multiple tables, separating frequently accessed columns from rarely used ones.

Create junction tables for tables that are often joined together.

Optimizer tuning

Enable Multi‑Range Read (MRR) to convert random disk reads into sequential reads, reducing I/O.

Enable disk pre‑read to fetch adjacent pages together, improving locality.

Architecture optimization

Apply read/write splitting: use a primary instance for writes and replica(s) for reads.

Summary steps

Configure the slow query log (via my.ini or runtime command).

Analyze the generated slow‑query log.

Locate inefficient SQL with show processlist;.

Run EXPLAIN to check index usage and execution plan details.

Apply optimizations: improve indexes, rewrite SQL, adjust schema, tune the optimizer (e.g., MRR), and consider architectural changes such as read/write splitting.