Re: Make optimized LEFT JOIN with GROUP BY

[Beat Vontobel]

Hi Jerry,

I suppose your query looks something like this:

SELECT recipe_id, MAX(picture_id) FROM recipes LEFT JOIN pictures USING (recipe_id) GROUP BY recipe_id

So did you try to add an index to your pictures table spanning _both_ rows, like so KEY (recipe_id, picture_id)? I think this should help, at least in fairly recent versions of MySQL.

Cheers,
Beat

Beat Vontobel
http://www.futhark.ch/mysql