How to Diagnose and Optimize Extremely Slow MySQL Queries

What Is a Slow SQL?

A slow SQL is a query whose execution time is noticeably long for the given application scenario; the threshold varies with workload, such as hundreds of milliseconds for a simple lookup or several seconds for bulk inserts. Slow queries consume resources, increase load, and can cause lock contention, so they should be monitored regularly.

How to Detect and Analyze Slow SQL?

Common detection methods in MySQL include reviewing the built‑in slow‑query log, using EXPLAIN to view execution plans, and enabling profiling to obtain detailed runtime metrics. Many organizations also use monitoring platforms that aggregate slow‑query logs, generate alerts, and suggest optimizations.

How to Optimize a Slow SQL?

The article examines a real example that took over 600 seconds to run, querying message records and user tables with large data volumes and a join operation. The query scanned millions of rows, leading to the extreme latency.

First, the query structure was reviewed; while syntactically correct, minor changes such as converting LEFT JOIN to INNER JOIN or using sub‑queries offered limited benefit.

The primary optimization leverages indexing. Adding an index on the createTime column of the message_record table allows MySQL to narrow the scan range, which can be verified with EXPLAIN showing index usage.

If indexing alone does not achieve acceptable performance, consider higher‑level approaches:

Business‑level review: Question whether the query is necessary in its current form; perhaps the requirement can be satisfied with fewer data points or a different aggregation.

Programmatic optimization: Split the query into smaller time‑range batches (e.g., daily), filter by specific user IDs, or execute multiple queries in parallel threads.

Caching: Store frequently accessed results in Redis or in‑memory caches to avoid repeated database hits.

Scheduling adjustments: Run heavy queries during off‑peak hours or stagger their execution to reduce impact on user‑facing traffic.

Partitioning: Partition the message_record table by day, creating separate tables for each date to limit the amount of data scanned per query.

Archiving: Move logically deleted rows to an archive table, similar to emptying a recycle bin, to keep the primary table lean.

Infrastructure upgrades: Tune MySQL configuration or consider a database engine optimized for analytical workloads, such as ClickHouse, when data volumes grow substantially.

Each of these techniques has trade‑offs; for example, partitioning introduces additional maintenance overhead, and switching databases incurs migration costs. Choose the most appropriate combination based on query patterns, data size, and operational constraints.