Enabling “log_queries_not_using_indexes” disables “long_query_time”?

Question

I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far less than long_query_time.

It seems that once the log_queries_not_using_indexes is enabled, Mysql will log all the queries that not using indexes no matter what the "long_query_time" is.

Is there a way to overcome this? I mean, is there a way to log all the queries which take longer time than long_query_time, regardless of whether they are using indexes?

Answer 1

That is expected behavior. Refer to the docs online, but in summary:

• long_query_time is the threshold for query execution time beyond which it is logged. Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.

• log_queries_not_using_indexes tells MySQL to additionally log all queries that do not use an index to limit the number of rows scanned. Logging on this condition happens regardless of execution time.

Hope that helps explain what you are seeing. It seems like you probably just want long_query_time and not log_queries_not_using_indexes if your goal is to only capture queries that take longer than a particular threshold.

Doc links: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time