Slow query log – Confusing information in manuals

by nivas on October 16, 2014

Below is the excerpt from MySQL manuals on slow query log :

“By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using log_slow_admin_statements and log_queries_not_using_indexes, as described later. “

This manual entry is kind of misleading . It means that queries not using indexes are not logged by default. Let us see what the reality is :

1) Let us see if queries not using indexes are being logged.

Below table does not have index:

mysql> describe employee ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show indexes from employee ;
Empty set (0.00 sec)


Query which does not use index :

mysql> select id from employee where id=2 ;
Empty set (0.00 sec)

Entry in the slow log :

# Query_time: 0.000216  Lock_time: 0.000108  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 76
SET timestamp=1413458016;
select id from employee where id=2;

2) Let us see if administrative statements are being logged

Analyze is an admin statement and it is being logged into slow log with default settings


# Query_time: 0.000053  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 33
SET timestamp=1413458411;
analyze table employee;

Conclusion:

Manual entry for slow log is confusing . If you set long_query_time to zero , all types of queries are logged by default whether they use indexes or not. Then what is the use of log_slow_admin_statements and log_queries_not_using_indexes ? They are useful when the long_query_time is set to a non-zero value . You will be able to log queries not using indexes and admin statements using these variables when long_query_time is set to non-zero value

Comments on this entry are closed.

Previous post:

Next post: