Make optimized LEFT JOIN with GROUP BY

[Jerry Potokar]

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 picture.date 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?

Yours

Jerry