Hands‑On MySQL Slow Query: Enable Logs, Analyze SQL, and Optimize Performance

What – Definition of a slow query

A slow query is an SQL statement whose execution time exceeds a preset threshold and degrades database performance. MySQL records such statements in the slow‑query log.

Why – Importance of detecting slow queries

Slow queries can monopolize CPU, exhaust connection pools, cause API timeouts, and lead to service outages. Investigating them is the first and most effective step in database performance tuning.

Where – Scenarios for enabling and checking slow queries

Before production release to catch inefficient SQL.

When API latency spikes or database CPU remains high.

For tables with more than 100 k rows or high‑concurrency events such as flash sales.

Small test datasets, offline batch jobs, or one‑off ad‑hoc queries are not priority scenarios.

How – End‑to‑end practical workflow

Step 1 – View default MySQL slow‑query settings

-- Check if slow query log is enabled
SHOW VARIABLES LIKE 'slow_query_log';

-- Check the threshold (default 10 seconds)
SHOW VARIABLES LIKE 'long_query_time';

-- Check log file path
SHOW VARIABLES LIKE 'slow_query_log_file';

By default the log is disabled and the threshold is too high to capture useful queries.

Step 2 – Apply production‑grade configuration (permanent)

# Enable slow query log
slow_query_log = 1
# Record queries slower than 200 ms
long_query_time = 0.2
# Log queries that do not use indexes
log_queries_not_using_indexes = 1
# Log file location
slow_query_log_file = /usr/local/mysql/log/slow.log

Restart MySQL for permanent effect; the same variables can be set dynamically for temporary testing.

Step 3 – Dynamically enable for local testing

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = ON;

Step 4 – Simulate a slow query

Using a 100 k‑row user_order table, run a full‑table‑scan statement without an index:

SELECT * FROM user_order WHERE status = 1 ORDER BY create_time DESC;

The statement appears in slow.log.

Step 5 – Core fields in the slow log

Query_time : execution duration; >0.2 s marks a slow query.

Rows_examined : number of rows scanned; larger values indicate higher risk.

Rows_sent : rows returned; a large scan‑to‑send ratio signals inefficiency.

Golden rule: if scanned rows far exceed returned rows, optimization is required.

Step 6 – Deep analysis with EXPLAIN

EXPLAIN SELECT * FROM user_order WHERE status = 1 ORDER BY create_time DESC;

The output shows type=ALL (full table scan) and Extra=Using filesort, a common and costly pattern.

Step 7 – One‑click optimization

Create a composite index covering the WHERE and ORDER BY columns:

CREATE INDEX idx_status_create ON user_order(status, create_time);

After re‑testing, execution time drops to 1‑5 ms, EXPLAIN shows type=ref/range without filesort, and the query no longer appears in the slow log.

Step 8 – Spring Boot integration for code‑level monitoring

Add the Druid connection‑pool starter:

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.2.20</version>
</dependency>

Enable SQL‑slow monitoring in application.yml:

spring:
  datasource:
    druid:
      filter:
        stat:
          slow-sql-millisecond: 200
          log-slow-sql: true
      stat-view-servlet:
        enabled: true
        login-username: admin
        login-password: 123456

Running the application allows real‑time viewing of slow SQL via Druid’s monitoring console, providing dual‑layer protection (database log + application monitoring).

High‑frequency slow‑query patterns & one‑click fixes

Full‑table scans: add or repair indexes.

Sorting/pagination bottlenecks: create a WHERE+ORDER BY composite index. SELECT *: query only required columns.

Large OFFSET pagination: use primary‑key‑based pagination.

Multi‑table joins: index join columns.

Functions on indexed columns: rewrite to avoid index loss.

Common pitfalls

Delaying optimization until the system is already overloaded.

Setting long_query_time too high; 200 ms is a practical production standard.

Focusing solely on execution time; high‑frequency short queries can also degrade performance.

Blindly adding indexes; excessive indexes hurt write performance.

Ignoring queries that lack indexes; they can cause sudden spikes under load.

Core takeaways

Slow queries are the root cause of database latency and API timeouts.

Production‑grade config: 200 ms threshold, log non‑indexed queries, monitor both DB and application layers.

Diagnosis focuses on Rows_examined, EXPLAIN output, and eliminating full scans/filesorts.

Optimization centers on precise indexing, avoiding index loss, selective column retrieval, and efficient pagination/sorting.

Combine MySQL slow‑query log with Druid monitoring for comprehensive protection.