Hm, just realized something:
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
You actually use receptislike.ID in the SELECT part, but have a GROUP BY only on columns from recepti. This is a bad idea (MySQL allows this, but it's non-standard and an evil thing): The receptislike.ID you get will actually be completely random, if there are more columns on the right side of the JOIN (in receptislike) for every row in recepti. You should always use an aggregate function on all columns that are not GROUPed BY (e.g. MAX() or MIN()) to keep this deterministic.
What's actually the purpose of all this? I understand you want only one row from the right side (receptislike)? Is that what you try to achieve with all the additional restrictions in the WHERE clause?
> I tried adding recepti.ID to the multi index, but
> doesn't help.
recepti.ID should definitely be in the index, as I understand it's the PRIMARY KEY and you use it in the JOIN and in the GROUP BY. So it should probably be the first column in the multi-column index on recepti.
> But as sson as I use GROUP BY recepti.datum
> instead of recepti.ID it uses where and the query
> is 100x faster... I don't understand the logic
> but... I can also use group by recepti.datum, but
> if 2 recipes would have same datetime one will not
> show in the list so I prefer the ID.
That's why you should use suggestion a) from above. See the explanations there.
> Is there any way to say mysql I want only one data
> from table pictures? Then I wouldn't need to use
> group by...
Which row from pictures do you want? Anyone? (What's the purpose then?) The last or first one? By date or by id? (Is "pictures" the same as "receptislike"? I don't see "pictures" in the query...)
If you just want to know if any rows on the right side exist, you could just use a COUNT(receptislike.ID). If you need the id but want to have the last or first one by another column in receptislike, we'll probably end up with a subquery.
Regards,
Beat
Beat Vontobel
http://www.futhark.ch/mysql