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