Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
curious...how's the load on the server? –  Derek Downey Aug 25 '11 at 20:14
    
less than 5, but it's possible it may have spiked during the incident. –  rsimoes Aug 25 '11 at 23:10
1  
Please show the query/queries that had this slow log behavior –  RolandoMySQLDBA Aug 26 '11 at 14:55
    
The only way the line Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739 can 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 these Query_time lines. –  RolandoMySQLDBA Aug 31 '11 at 22:09

5 Answers 5

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.

share|improve this answer
    
-1 for "that can be pushed to the application", +1 for everything else –  Jack Douglas Aug 26 '11 at 23:24

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

share|improve this answer

my idea is to diagnose the entire database with some deeper-monitoring tool - I'm using nms express (free). http://bit.ly/nwwdKS

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.