How to Quickly Locate and Optimize Slow Query SQL in MySQL

Hello everyone, I am Chen. Today I will share how to quickly locate slow‑query SQL statements and optimize them.

1. How to locate and optimize slow‑query SQL?

Generally there are three directions to consider:

Locate slow queries from the slow‑query log.

Analyze the execution plan with EXPLAIN and similar tools.

Modify the SQL or force it to use an index.

2. How to use the slow‑query log?

There are three steps:

1) Enable the slow‑query log

Set the slow_query_log variable to ON in the MySQL command line: set global slow_query_log=on; By default the slow‑query log is disabled, so you need to turn it on.

2) Set the slow‑query threshold

set global long_query_time=1;

If a statement runs longer than this threshold it will be recorded. The default is 10 s; for production a common setting is 1 s (or even 0.1 s for high‑QPS workloads). In test environments you may set a lower value, e.g., 0.5 s or 0.

Pay special attention to the Rows_examined column, which shows how many rows were read from the storage engine.

3) Determine the log file name and path

show global variables like 'slow_query_log_file';

The result shows that the default path is the MySQL data directory. You can also check the data directory itself: show global variables like 'datadir'; To see which variables are related to queries you can run: show global variables like '%quer%'; After setting long_query_time=1 you can verify the change with the same command.

Note that changing the variable at runtime is lost after a MySQL restart; you must edit the configuration file for a permanent change.

3. Slow‑query example (beginner‑friendly)

Table definition (≈2 million rows):

CREATE TABLE `person_info_large` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account` VARCHAR(10),
  `name` VARCHAR(20),
  `area` VARCHAR(20),
  `title` VARCHAR(20),
  `motto` VARCHAR(50),
  PRIMARY KEY (`id`),
  UNIQUE (`account`),
  KEY `index_area_title`(`area`,`title`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The slow‑query log shows a Query_time of 6.33 s for a SELECT that should only take a few seconds, indicating a problem.

Typical fields in a slow‑query entry:

Time : when the slow query occurred.

Query_time : execution time.

Lock_time : time waiting for locks.

Rows_sent : rows returned.

Rows_examined : rows read from the storage engine.

You can also use tools such as pt‑query‑digest or mysqldumpslow to analyse the log.

If a slow query is still running and not yet written to the log, use SHOW PROCESSLIST (requires PROCESS privilege) to see active threads.

4. What to do when a query is slow? Use EXPLAIN to analyse the plan

Running EXPLAIN on the example query highlights important columns: select_type – e.g., SIMPLE. type – e.g., index (full index scan) or ALL (full table scan). possible_keys – indexes that could be used. key – the index actually used. Extra – additional information such as Using index, Using filesort, or Using temporary.

If type = NULL, MySQL can return the result without accessing any table or index.

When Using filesort or Using temporary appears, the optimizer cannot use an index efficiently, and performance will suffer.

5. When primary key, unique key, and regular indexes all exist, how does the optimizer choose?

Running SELECT COUNT(id) FROM person_info_large shows that the optimizer picks the unique index on account because its leaf nodes contain less data than the clustered primary key.

You can force a different index with FORCE INDEX and compare execution times (e.g., unique index ~676 ms vs. primary key ~779 ms).

In practice, you may need to override the optimizer’s choice based on your workload.

Note: The author also includes a promotional line about WeChat friend limits, which is unrelated to the technical content.