title: Why does this query cause lock wait timeouts?
body: <p>Our team just spent the last week debugging and trying to find the source of many mysql lock timeouts and many extremely long running queries.  In the end it appears that this query is the culprit.  </p>
  
  <pre><code>mysql&gt; explain 
  
  SELECT categories.name AS cat_name, 
  COUNT(distinct items.id) AS category_count 
  FROM `items` 
  INNER JOIN `categories` ON `categories`.`id` = `items`.`category_id` 
  WHERE `items`.`state` IN ('listed', 'reserved') 
     AND (items.category_id IS NOT NULL) 
  GROUP BY categories.name 
  ORDER BY category_count DESC 
  LIMIT 10\G
  
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: items
           type: range
  possible_keys: index_items_on_category_id,index_items_on_state
            key: index_items_on_category_id
        key_len: 5
            ref: NULL
           rows: 119371
          Extra: Using where; Using temporary; Using filesort
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: categories
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: production_db.items.category_id
           rows: 1
          Extra: 
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>I can see that it is doing a nasty table scan and creating a temporary table to run. </p>
  
  <p>Why would this query cause database response times to go up by a factor of ten and some queries that usually take 40-50ms (updates on items table), to explode to 50,000 ms and higher at times?</p>