MySQL sub-query optimization

Question

I've got a query that is running awfully slow:

SELECT * 
FROM games
WHERE games.platform =13
AND games.id NOT 
IN (SELECT game_id
FROM collections
WHERE collections.user_id =1)

I attempted to rewrite it as a left join but it's returned 0 results:

SELECT * 
FROM games 
LEFT JOIN collections ON collections.game_id = games.id 
WHERE collections.game_id IS NULL AND collections.user_id = 1 AND games.platform = 13
ORDER BY games.name ASC

Could someone point out my error here?

try that using #FULL OUTER JOIN because it will point to you everything, and what does not match will return as null. — devasia2112
– devasia2112, Commented Mar 17, 2011 at 19:59

Pentium10 · Accepted Answer · 2011-03-17 20:07:43Z

3

SELECT games.* 
FROM   games 
       LEFT JOIN collections 
         ON collections.user_id = 1 
            AND collections.game_id = games.id 
WHERE  games.platform = 13 
       AND collections.game_id IS NULL 
ORDER  BY games.name ASC

you need indexes on

(games.id,platform,name)
(collections.user_id,collections.game_id)

edited Mar 17, 2011 at 20:07

answered Mar 17, 2011 at 19:59

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RichardTheKiwi Over a year ago

I think the best indexes are games(id) and collections(user_id, game_id) select * makes the rest moot. Consider also changing select * to select games.*

madzilla Over a year ago

You guys are rockstars! Thanks.

Collectives™ on Stack Overflow

MySQL sub-query optimization

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related