title: MySQL JOIN with COUNT performance
body: <p>I've recently been trying to optimise our database to increase its scalability, and run into something that I've been unable to further optimize, but feels like there should be <em>something</em> I can do better. I currently have:</p>
  
  <pre><code>SELECT b.id, b.title, COUNT(p.id) AS count
  FROM brands b
  LEFT JOIN products p ON b.id = p.brand_id
  WHERE p.status = 'Accepted'
  GROUP BY b.id
  </code></pre>
  
  <p>I will potentially be adding extra <code>WHERE</code> clauses to further filter products, so I've ruled out keeping a maintained count of accepted products on each brand record (which is probably a bad way to do things anyway).</p>
  
  <p>The tables are <code>MyISAM</code>, <code>b.id</code>, <code>p.brand_id</code> and <code>p.status</code> are indexed. With ~500,000 products and ~1,000 brands, it's taking in the region of 2.5s. Is there something better I could be doing to get a list of brands with their product counts, or is that time-frame actually pretty reasonable for what I'm trying to achieve?</p>