Mysql – Enabling “log_queries_not_using_indexes” disables “long_query_time”

MySQLmysql-5.5performanceslow-log

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?

Best Answer

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

Related Solutions

MySQL – How Often is Slow_Log Table Updated?

The slow query log gets updated once a query is completed and the query time is beyond or equal to value set in long_query_time.

So it could mean either good or bad. The good is all the queries completed within long_query_time. The bad is the slow query is still running.

To experience a slow query you can try subqueries with multiple level.

MySQL – Why Are Slow Queries Not Logging?

I think I got the answer:

You need to put those options under the [mysqld] section

[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

and restart mysql

UPDATE 2013-03-05 16:36 EST

I don't know why this is still happening, but please try this:

service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql start

then run SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.log

Best Answer

Related Solutions

MySQL – How Often is Slow_Log Table Updated?

MySQL – Why Are Slow Queries Not Logging?

UPDATE 2013-03-05 16:36 EST

Related Question