MySQL Slow Query Log – Long Query


I enabled slow-query-log on my server, and set long_query_time to 10 seconds.

But what strange is, that queries that are taking in execution 0.0002 seconds are also being logged in my log file, and now my log file is about 100Gb , which means mysql is logging all queries.

I have look at the mysql-slow.log file and i haven't seen any query taking more than 1 second, note that long-query-time is 10 seconds and what is expected is to find queries that have taken 10 seconds and more in execution

An Example

# Query_time: 0.000278  Lock_time: 0.000072 Rows_sent: 28  Rows_examined: 65

Any advisors please

Best Answer

Turn OFF log_queries_not_using_indexes; it is virtually useless and can cause what you experienced.

If it is already off, then please show us one entire slowlog entry, together with the CREATE TABLE for the table(s) that are involved. Also, what version are you using?

On most systems it is best to set long_query_time to no more than 2.