MySQL Forums
Forum List  »  Optimizer & Parser

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

I am using the 5.0.21 version right now, yes...

First of all, congratulations... The most fascinating thing is you taught me that I don't need ORDER BY. I didn't know that GROUP BY can have ASC, DESC etc... and it also sorts...

By using group by datum, ID I guess it sorts by both parameters and since ID is unique to every recipe it doesn't group by the same dates I have used in many recipes...

Works great, but the explain says I don't use index at all, but explain must be wrong, as the speed is 20ms. It is working (without index, it takes 0.11 seconds) for 10 rows. I trust the result not the Explain I guess...

I have some observations:

1. If I use INDEX instead of UNIQUE it doesn't change anything.
2. I don't use index on both columns on picture table only on ID and it still works OK.

My list is in such a manner that visitor can click on the different columns (author etc.) and it gets sorted on another column. I guess this "trick" of yours works only on the datum DESC, ID desc as you wrote. If visitor will order by another field it will take longer and there is nothing I can do about it I guess (can't have 10 indexes).

Someone I know suggested me to make this list into a file and read the file when visitor comes to the page. I am not sure if reading file is really faster than database search. He tells me reading from database if the data don't change in real time (I put new recipes on only once daily) is a vaste of resources.

Thanks again for your help.


Options: ReplyQuote

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

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.