How to Locate and Optimize Slow SQL Queries in MySQL

1. How to locate and optimize slow SQL queries

Typically consider three aspects: locate slow SQL via the slow query log, analyze the execution plan with EXPLAIN, and modify the SQL to make it use indexes.

2. How to use the slow query log

Enable the log by setting slow_query_log on, adjust long_query_time (commonly to 1 s), and specify the log file path using slow_query_log_file. Example commands:

set global slow_query_log=on;
set global long_query_time=1;
show global variables like 'slow_query_log_file';
show global variables like 'datadir';
show global variables like '%quer%';

After configuration, restart MySQL or edit the configuration file for persistence.

3. Slow query example demonstration

A sample table person_info_large with about 2 million rows is created, containing primary key, unique, and composite indexes.

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 on name without an index shows a Query_time of 6.33 s; after adding an index the time drops to roughly 3.48 s.

4. Analyzing execution plan with EXPLAIN

Key columns to examine are select_type, type, possible_keys, key, and Extra. For example, type=index indicates an index scan, while type=ALL means a full table scan. Presence of Using filesort or Using temporary signals inefficient operations.

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

When counting rows, the optimizer may pick the smallest covering index (e.g., the unique account index) rather than the primary key because its leaf nodes contain fewer columns. The FORCE INDEX hint can be used to test alternative choices.

Overall, enabling the slow query log, setting appropriate thresholds, analyzing EXPLAIN output, and adding suitable indexes are essential steps to improve MySQL query performance.