Databases
StackOverflow2013
Postsdbo
POOptimizing MySQL query with multiple left joins
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimizing MySQL query with multiple left joins
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload