13

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?

27

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

  • 5
    MySQL should allow logging each of these two types of queries to their own file. I've come up with a technique for finding the lines where the the query time is listed which makes it easy to visually scan for the slow queries but it would be nicer to have separate files. – Night Owl Apr 27 '13 at 1:25
  • what does is it means when particularly mentioning here : "Logging on this condition happens regardless of execution time." ? – user10089632 Mar 1 '18 at 10:26
  • @user10089632 read the doc under the first link above. – dezso Mar 1 '18 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.