Re: Make optimized LEFT JOIN with GROUP BY
Thank you for your answer (again!)...
My site is not purely updated once a day, it is dynamic (forum, opinions). Just the recipes are validated by me and put on the site once a day. So I would just need to put the last 10 recipes into a file and read it on my portal. But 20ms for this query is not too bad (my site is big for our small country, but is not big in the real world :-)). I better not tell you I am using (still, no time to preprogram) ASP... But it works, although everyone thinks I am crazy using mysql + ASP on Windows.
Do you think I should leave the last 10 recipes in reading the 20ms query or from a file on disk? I guess reading from disk would save the database 20ms every time but there would be more disk access...
About the sorting, many sites can sort columns... How they do that? I found a way to use sort... I can dynamically make queries of the type:
group by datum DESC, ID DESC (ASC also works fast) :-))
group by RecipeName ASC, ID ASC ( there are recipes with same name that I don't want to loose in the list)...
And I make index1: Datum, ID
And make index2: RecipeName, ID
etc...
I think making about 5 such indexes is not too much, but then again update and insert would take... will need to update ID 6 times (ID is a primary key also). But I have to use ID as the second group by column because without it I can loose some rows :-(
I tried what I wrote now and doesn't work. I have to FORCE index2, index3... Seems it allways want to take index1 (datum, ID), so I would have to dynamically force index on Recipes and change the group by column. That is not so much work, but I am worried having 6 idexes on ID. But then again, I have only 5 Mb of recipes (6000+) so it is not such a big table.
Yours
Jerry
I think the key point I was missing (of course not only that one) was that I didn't know one can use GROUP BY as a sort order with ASC, DESC... With the help of the ID not to loose data... I think that "trick" is really something... My list of 50 recipes now takes 0.02 sec instead of 0.22 :-) And I also have the picture ID's which I will use for the click. Now if I wanted to show 3 pictures by the recipe that has 3 pictures, I think I would be stuck again :-)
Edited 2 time(s). Last edit at 05/03/2006 05:13PM by Jerry Potokar.