Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Yes, Simply dont use log-queries-not-using-indexes or set it OFF SET GLOBAL log_queries_not_using_indexes='OFF'; –  Frederic Yesid Peña Sánchez Jan 22 '13 at 16:41
add comment

1 Answer 1

up vote 6 down vote accepted

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 returned. 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

share|improve this answer
1  
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
    
+1, excellent explanation –  Sukan Jun 20 at 4:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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