title: How to interpret slow query log information generated by MySQL?
body: <p>So my understanding of slow query log is that it logs info about all those queries that took >= time (in secs) that we have set in my.conf file.</p>
  
  <p>Now lets take 3 cases of 3 different SELECT queries (against tables with INNODB engine):</p>
  
  <p><strong>QUERY I:</strong> Query_time: 32.937667  Lock_time: 0.000081 Rows_sent: 343  <strong>Rows_examined:</strong> 12714043</p>
  
  <p><strong>QUERY II:</strong> Query_time: 12.937667  Lock_time: 0.000081 Rows_sent: 43  <strong>Rows_examined:</strong> 714043</p>
  
  <p><strong>QUERY III:</strong> Query_time: 42.937667  Lock_time: 0.000081 Rows_sent: 18  <strong>Rows_examined:</strong> 483</p>
  
  <p>To me both QUERY I and QUERY II looks like possible cases of a bad query or poor indexing (or missing indexing) or fragmented table data etc. (anything else that I might have missed?) that user may look at to improve query execution time.</p>
  
  <p>But for the QUERY III, I am not able to get my head around, I mean what could really be wrong with the DB that it takes 42 secs to just examine 483 rows and sent back 18 of them (with a negligble lock time). This becomes even more confusing when I see it happening intermittently.</p>
  
  <p>So what I really want to ask here is:</p>
  
  <ul>
  <li>how should I interpret lock time info? Does it mean query had to wait for that many secs before it actually started executing? If yes, then in my example query III actually took 42 secs to examine 483 rows and sent back 18 of them?</li>
  <li>if lock time is neglible but still query time is super huge with just few hundreds of rows being examined and sent back, where should I start looking for issues?</li>
  <li>Could it be that query is spending to much time in some background IO activity? say logging or bin-logging.</li>
  <li>How badly could size of table affect the performance of query? e.g. can we say MySQL is good enough to handle table with 200+million rows</li>
  <li>Is there any better tool or way to monitor DB activity specially to figure background activity of DB? In short, to check where is that query spending most of it's time.</li>
  </ul>
  
  <p>There might be lot many factors affecting such slow queries, so in case you feel that you need some more information from side to be able to help me then please do let me know.</p>