title: how do you optimize this semi-complex mysql query?
body: <p>Greetings fellow flowstackers!</p>
  
  <p>Here's the db schema (reduced to relevant fields) to help illustrate my conundrum:</p>
  
  <p><img src="https://i.stack.imgur.com/rXVzc.png" alt="enter image description here"></p>
  
  <ul>
  <li>A webstore has <code>items</code>, and <code>items</code> have <code>item_options</code>.</li>
  <li>The same <code>item</code> can appear in multiple <code>categories</code>.</li>
  <li><code>categories</code>, <code>items</code> and <code>item_options</code> can all be active or inactive (BOOL).</li>
  </ul>
  
  <p>Categories look like this (notice the parent_id nesting, where Fruit Seeds are inside of Seeds):</p>
  
  <pre><code>id   parent_id   name              active
   1           0   Seeds                  1
   2           1   Vegetable Seeds        1
   3           1   Fruit Seeds            0
   4           0   Plants                 1
   5           4   Vegetable Plants       1
   6           4   Fruit Plants           1
  </code></pre>
  
  <p>What I want is a fast list of all active categories (id, parent_id and name), and the count of each category's active items containing active item_options.</p>
  
  <p>The query result would look like this:</p>
  
  <pre><code>id   parent_id   name              item_count
   1           0   Seeds                      0
   2           1   Vegetable Seeds           52
   4           0   Plants                     0
   5           4   Vegetable Plants         103
   6           4   Fruit Plants              79
  </code></pre>
  
  <p>This query works, <b>but takes ~430ms</b>:</p>
  
  <pre><code>    SELECT c.`id`, c.`parent_id`, c.`name`,
          (SELECT COUNT(*)
              FROM `item_categories` AS ic
              LEFT JOIN `items` AS i
                  ON (i.`id` = ic.`item_id`)
              LEFT JOIN `item_options` AS io
                  ON (i.`id` = io.`item_id`)
              WHERE c.`id` = ic.`category_id`
                  AND i.`active` = 1
                  AND io.`active` = 1
              ) AS `item_count`
      FROM `categories` AS c
      WHERE c.`active` = 1;
  </code></pre>
  
  <p>This next query <b>takes only ~55ms, but fails</b> to include the top-level categories (where <code>parent_id</code> = 0):</p>
  
  <pre><code>    SELECT c.`id`, c.`parent_id`, c.`name`,
          COUNT(ic.`item_id`) AS `item_count`
      FROM `categories` AS c
      LEFT JOIN `item_categories` AS ic
          ON (c.`id` = ic.`category_id`)
      LEFT JOIN `items` AS i
          ON (i.`id` = ic.`item_id`)
      LEFT JOIN `item_options` AS io
          ON (i.`id` = io.`item_id`)
      WHERE c.`active` = 1
        AND i.`active` = 1
        AND io.`active` = 1
      GROUP BY c.`id`;
  </code></pre>
  
  <p>Anyone see how to speed up the first query, or fix the second one?</p>