MySQL Forums
Forum List  »  Optimizer & Parser

Re: Make optimized LEFT JOIN with GROUP BY
Posted by: Jerry Potokar
Date: May 03, 2006 02:38PM

Beat Vontobel wrote:
> 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.

Hm, I do use MAX now... I can also group by receptislike.IDrecept as this is the same as recepti.ID (this is the join ID). I tried doing it this way but also didn't work. I tried all that you said, made index on all the where and then group by and order by columns. I also added the recepti.ID to the group by, didn't work.

Only thing it works (I mean FAST) is that I use

group by recepti.ID
order by recepti.ID DESC or

group by recepti.datum
order by recepti.datum desc

The first means that my recipes will be sortd by the ID of the arrival not by date that I set them to show (datum). The second sorts OK, but it means if I have 2 recipes I set same date and time (and unfortunately I do have such recipes) I loose one of the recipe :-(

> 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?

Yes, receptislike is in English recipespicture :-) I want to make a list of all my recipes that shows name of the recipe and the small icon for the picture. That picture is one of the recipe pictures (I didn't care which one, but using MAX(receptislike.ID) I can make it the newest picture :-) In this list I want one recipe to show only once, not as many times as the number of pictures that it has. It shows if I don't have a picture and it shows if the picture is one. But if there are 2 or more pictures I don't know how to do it not to use the group by parameter.

The other restrictions have nothing to do with the problem (I removed them and still same problem). They in fact mean jezik=1 (which language is the recipe), slovnica = 1 (is the grammar validated) and datum<date() means that I only list the recipes that are set to show (some have future dates so they show the next days).

> > 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...)

Yes, receptislike = RecipesPictures in English :-)

The perfect solution would be to get the ID of the picture so I can make a link to open picture in the list :-) If not possible I would at least want to know if a recipe has picture or not. That way I can put a small icon in the list that shows recipe has at least one picture.

So best would be receptislike.ID as they are sorted by date ovbiously. So the highest ID is the last picture for that recipe. I would like that one to get the ID for.

> 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.

I'll try the count variant :-) You gave me idea, maybe there is a hi speed solution to this using the subquery. Problem is I have never used a subquery. The truth is I am not an expert, I made a culinary site and it has 10.000 visitors per day so my bad queries are starting to show off and I am optimizing what I can.

Thank you very much for your kind help!


If you can send me your Email to my email I will send you my site URL, so you can see the problem. I already have a list, but it is not very fast :-(

Edited 2 time(s). Last edit at 05/03/2006 02:46PM by Jerry Potokar.

Options: ReplyQuote

Written By
Re: Make optimized LEFT JOIN with GROUP BY
May 03, 2006 02:38PM

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.