Slow Query logging everything

Question

I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example) Query_time: 0.000237 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: 109

On another server I get these: Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

Either way they both seem to be logging other queries...or am I reading something wrong?

set-variable = log-slow-queries="/tmp/mysql-slow-query.log"
   set-variable = long_query_time=1

Answer 1

If you're using mysql 5.1.21 or newer, you can change the behavior of logging slow queries with the min_examined_row_length

Set it to something like 5000 or so, and keep your log_queries_not_using_indexes turned on.

Answer 2

You should raise the long_query_time to 10 (the default value)

MySQL 5.1 allows sub-second granularity of the long_query_time, which may explain why you see decmial notation for the first slow query log display.

You may also want to check and make sure that log_queries_not_using_indexes is not enabled. Otherwise, any old query not using indexes will bypass the long_query_time setting and get recorded in the slow log.

CAVEAT

set-variable is deprecated in MySQL 5.1

This is from MySQL 5.1.37

  -O, --set-variable=name
                      Change the value of a variable. Please note that this
                      option is deprecated;you can set variables directly with
                      --variable-name=value.