MySQL slow query slow query log and unused index (not using Indexes) queries configuration and use

MySQL's "slow query" refers to an SQL statement that exceeds the maximum allowable query time ( long_query_time ), and the "unused index" query, by definition, is the SQL statement that the query statement does not use to the index.

Slow query configuration and use

Add the following parameters to the Msyqld startup configuration file or command line parameters

Long_query_time=1

Log-slow-queries=/var/mysql/logs/slow.log

The Long_query_time parameter represents the measured time of the slow query, in seconds, the minimum is 1, the default value is 10, and any SQL statement that executes longer than Long_query_time is recorded in the slow query log.

--log-slow-queries[=file_name]The file_name parameter is optional, the default value is host_name-slow.log , if the file_name parameter is specified, MySQL will log the slow query to the file_name set of files, if file_name provides a relative path, MySQL logs the log to the MySQL data directory.

Not using index queries to configure and use

Adding parameters to MySQL's boot configuration file or command line parameters --log-queries-not-using-indexes allows you to enable unused index query statements, which are the files that are log-slow-queries corresponding to the log records.

Mysql.conf

log-queries-not-using-indexes

Use case

MySQL is configured as follows

[Mysqld]

#Slow Query Log
Long_query_time=3

Log-slow-queries

# Log queries that's not using indexes
Log-queries-not-using-indexes

Execute the following command

Select Sleep (1);

Select Sleep (3);

SELECT * from T1;

The log contents are as follows

D:\MySQL\MySQL Server 5.1\bin\mysqld, Version:5.1.31-community-log (MySQL Community Server (GPL)). Started with:
TCP port:3306, Named Pipe: (NULL)
Time Id Command Argument
# time:090625 12:58:09
# [email protected]: root[root] @ localhost [127.0.0.1]
# query_time:3.000077 lock_time:0.000000 rows_sent:1 rows_examined:0
SET timestamp=1245905889;
Select Sleep (3);
# time:090625 12:58:42
# [email protected]host:root[root] @ localhost [127.0.0.1]
# query_time:0.046876 lock_time:0.031251 rows_sent:0 rows_examined:0
SET timestamp=1245905922;
SELECT * from T1;

Other

In addition to some special slow query log analysis, query tools, we can google the following relevant content.

MySQL slow query slow query log and unused index (not using Indexes) queries configuration and use