title: MySQL query randomly hangs on 'sending data' status?
body: <p>I am using a MySQL 5.5.25 server, and InnoDB for my databases.</p>
  
  <p>Quite often the CPU of the server is working at 100% due to a mysqld process for roughly a minute. Using SHOW PROCESSLIST:</p>
  
  <pre><code>Command | Time | State        | Info
  Query   |  100 | Sending data | SELECT a.prefix, a...
  Query   |  107 | Sending data | SELECT a.prefix, a...
  Query   |   50 | Sending data | SELECT a.prefix, a...
  </code></pre>
  
  <p>The problematic query is:</p>
  
  <pre><code>SELECT a.prefix, a.test_id, a.method_id, b.test_id
  FROM a
  LEFT JOIN b ON b.test_id = a.test_id
  AND user_id = ?
  AND year = ?
  </code></pre>
  
  <p>All these columns are INDEXED, thus this ain't the problem. Also, when I run the query in phpMyAdmin (with a sufficient LIMIT), it takes 0.05 seconds to complete. Also, it is quite frustrating that I find it to be impossible to reproduce this problem myself, even when executing this query twice simultaneously and spamming it only gives me spikes to 40% CPU.</p>
  
  <p>Prefixing the query with EXPLAIN results in:</p>
  
  <pre><code>Id | select_type | table | type | possible_keys        | key     | key_len | ref          | rows | Extra
   1 | SIMPLE      | a     | ALL  | NULL                 | NULL    | NULL    | NULL         | 1169 | 
   1 | SIMPLE      | b     | ref  | user_id,year,test_id | test_id | 4       | db.a.test_id |   58 | 
  </code></pre>
  
  <p>Unless I cannot see what is right in front of me, I am looking for ways to discover how to debug this kind of problem. I do already log all queries with their time to execute, and their values etc. But since I cannot reproduce it, I am stuck. What should I do to figure out what this problem is about?</p>