title: Optimizing MySQL query with multiple left joins
body: <p>I have a NewsStories tables which I'm left joining with some related tables. Each News story can have multiple images, categories and addresses. So the query is essentially:</p>
  
  <pre><code>SELECT * FROM NewStories 
  
  LEFT JOIN Images ON Newstories.id=Images.story_id
  
  LEFT JOIN Categories ON NewsStories.id=Categories.story_id
  
  LEFT JOIN Addresses ON NewsStories.id=Addresses.story_id
  
  WHERE ...
  </code></pre>
  
  <p>There are usually a few images and addresses per story, and 1 or 2 categories. The NewsStories table has about 10,000 articles.</p>
  
  <p>The trouble is that the performance is rather slow (in the order of 15-20 seconds, although it does vary quite a bit and sometimes drops to as low as 5 seconds).</p>
  
  <p>I was wondering if there's a better way of organizing the query to speed it up (I'm quite new to SQL).</p>
  
  <p>In particular it seems quite wasteful that the number of rows for a given story is multiplied by the number of images times the number of addresses times the number of categories.</p>
  
  <p>I'm essentially trying to reconstruct the properties of the News story into a single object which I can manipulate in a frontend.</p>
  
  <p>Here's the explain (apologies if the formatting doesn't come out correctly). I'm guessing I'm not indexing Addresses properly if it's "Using where". Is that correct?</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  
  1   SIMPLE  Addresses   ALL NULL    NULL    NULL    NULL    6640    Using where
  
  1   SIMPLE  NewsStories eq_ref  PRIMARY PRIMARY 767 NewsStories.Addresses.story_id 1    Using where
  
  1   SIMPLE  Images  ref PRIMARY PRIMARY 767 NewsStories.NewsStories.id  1   Using index
  
  1   SIMPLE  Categories  ref PRIMARY PRIMARY 767 NewsStories.NewStories.id   1
  </code></pre>