How to Quickly Identify and Optimize MySQL Slow Queries

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

There are three main approaches:

Locate slow queries from the slow‑query log.

Use EXPLAIN and similar tools to analyze the execution plan.

Rewrite the SQL or force it to use an index.

2. How to use the slow‑query log?

Step 1 – Enable the slow‑query log

Set the slow_query_log variable to ON:

set global slow_query_log=on;

Step 2 – Set the slow‑query threshold

Define the time threshold (in seconds) that qualifies a query as slow. The default is 10 s; a common production value is 1 s: set global long_query_time=1; In testing environments you may use a smaller value, e.g., 0.5 s, or even 0 to log every statement.

Step 3 – Determine the log file location

Query the variable that stores the log file path:

show global variables like 'slow_query_log_file';

Check the MySQL data directory to confirm the default path:

show global variables like 'datadir';

3. Slow‑query example demonstration

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;

Running a SELECT that scans the whole table shows a Query_time of over 6 s, which exceeds the 1 s threshold and is recorded in the slow‑query log.

Key columns in the EXPLAIN output:

select_type : type of SELECT (e.g., SIMPLE).

type : access method (e.g., index, ALL).

possible_keys : indexes that could be used.

key : index actually used.

Extra : additional info such as Using index, Using filesort, Using temporary.

If type is ALL or Using filesort / Using temporary appears, the query is not using an optimal index and should be tuned.

4. Using EXPLAIN to analyze query plans

For the table above, the account column has a unique index. An EXPLAIN of a query that orders by account shows type = index and Using index, indicating an index‑only scan.

When the name column is indexed, the execution plan changes from type = ALL (full table scan) to type = index (index scan), and the Extra column shows Using index instead of Using filesort, reducing query time from ~6.3 s to ~3.5 s.

5. How the optimizer chooses among primary, unique, and secondary indexes

Running SELECT count(id) FROM person_info_large; uses the unique account index because its leaf nodes contain only the primary‑key pointer, making it smaller than the clustered primary index.

You can force the optimizer to use a specific index with FORCE INDEX and compare execution times; the optimizer’s choice is usually faster, but manual tuning may be needed for special cases.

Overall, enabling the slow‑query log, setting an appropriate long_query_time, and using EXPLAIN to understand and adjust indexes are essential steps for MySQL performance tuning.