How Do I Query SQL Statements in MySQL that Are Executed with Inefficient Execution?

1. MySQL database has several configuration options that can help us capture inefficient SQL statements in a timely manner1, Slow Query Log

If this parameter is set to on, you can capture SQL statements whose execution time exceeds a certain value.

2，long_query_time

If the execution time of an SQL statement exceeds this value, it will be recorded in the log, and it is recommended that you set it to 1 or shorter.

3. The file name of the slow query log file.

4. If the log queries not using indexes parameter is set to on, it can capture all SQL statements that do not use indexes, although this SQL statement may be executed quite quickly.

2. Methods to detect the efficiency of SQL statements in MySQL 1.

1) Open MySQL under Windows and query the MySQL configuration file in the Windows system is generally to find [MysqlD] and add it below.

**As follows. log-slow-queries = f:/mysql/log/long_query_time = 2

You can use MySQL's built-in slow log analysis tool to query SQL statements that are inefficient in MySQL. Log is a file written by MySQL based on the execution time of SQL statements to analyze slow statements.

The configuration method is as follows:

1. Open the MySQL installation directory, find the file, open it, and enter the following configuration:

log-slow-queries = [slow_query_log_filename]

You can record SQL statements that exceed the default 10s execution time.

If you want to modify the default settings, you can add:

long_query_time = 5

Set to 5s.

2. In the process of SQL execution, use explain to understand the status of SQL execution.

Explain shows how MySQL uses indexes to handle select statements and join tables. It can help you select better indexes and write more optimized query statements.

To use it, add explain before the select statement

For example, explain select surname, first name form a, b where