Re: Make optimized LEFT JOIN with GROUP BY

[Jerry Potokar]

Huh, in fact it is like that:

SELECT recepti.ID, recepti.ImeRecepta, recepti.Datum, receptislike.ID AS IDslike
FROM recepti
LEFT JOIN receptislike ON recepti.ID = receptislike.IDrecept
WHERE slovnica = 1 and validirano = 1 and datum < date() and jezik=1
GROUP BY recepti.ID
ORDER BY recepti.Datum DESC

Datum means date in my language :-)

Here is the explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE recepti ref recepti_multi recepti_multi 2 const 2974 Using where; Using temporary; Using filesort
1 SIMPLE receptislike ref receptislike_idx receptislike_idx 4 kulinarika.recepti.ID 1

Here is the recepti_multi index:

recepti_multi INDEX 5947 slovnica
Datum
jezik
Kategorija
Validirano