MySQL Forums
Forum List  »  Optimizer & Parser

Make optimized LEFT JOIN with GROUP BY
Posted by: Jerry Potokar
Date: May 03, 2006 08:30AM

Hi there

I have been struggling this problem a logn time now... I won't post all the data as it is more of a theoretial question, I have many queries like that...

I have 2 tables, recipes and pictures. Obviousla recipe can have more than one picture so I join tables using left join. What I would like to achieve is a list of the recipes with a mark where pictures are available. But only one of the pictures (the one with the latest ID).

My problem is I get rows like this:

recipe1 picture1
recipe1 picture2 etc.

where I have more than one picture in a recipe. That is normal for left join I know... So I use a group by (recipe.ID) so I get only one recipe out with one picture for each row. It works, but my index oon recipe.ID isn't working, as it sorts the results using filesort and temporary, which is not good...

I have an order by from picture table but even if I don't use it, still doesn't work. And I have index on the picture.ID column.

How do you make a left join with group by without filesort an temporary?



Options: ReplyQuote

Written By
Make optimized LEFT JOIN with GROUP BY
May 03, 2006 08:30AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.