I have a slow query log item with the following:
Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739
No one in my group can explain it besides a guess about packet loss. Is there a common cause or set of causes for result like the above?
I'm going out on a limb here, but did you do an 'EXPLAIN' on this query?
If not, run "EXPLAIN" to see what the execution plan is. This will give you hints on optimization or indexing.
E.g. SOMETHING LIKE
EXPLAIN SELECT a.field1,b.field1 FROM tableA a LEFT JOIN tableB b ON b.a_id = a.id;
The result will be a nicely formatted report.
You may find there's unnecessary complexity in the query that can be pushed to the application or just factored out.
Do you have the query cache enabled? I found this (closed) bug and one reason given for the long behavior was an improperly configured query cache.
http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html
I think it's network latency. I would see queries in my slow-log that I ran directly on the server and they would return immediately. After scratching my head for a while, I tried logging in remotely to the server and running a query that would return data. The query then took much longer to return. If you turn on profiling, you can see that the greatest amount of time was spent in 'sending data'.
my idea is to diagnose the entire database with some deeper-monitoring tool - I'm using nms express (free). http://bit.ly/nwwdKS
I'd say this really depends on the query itself, while you're only scanning 739 rows in your table and returning all 739, it depends on a few things. One how much RAM mysql had to work with for that thread/query. Also it depends on the query itself. Imagine a query with 20 inner joins and another 20 where clauses your result set would be limited but the amount of work to pair down those result sets would definitely be significant even if the result set is relatively small.
Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739can be registered in the slow log is if a query came to completion. Please show the query/queries that are listed directly underneath each of theseQuery_timelines.