MySQL Forums
Forum List  »  Optimizer & Parser

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

I am sorry to ask you here, this is another topic, but you are so smart :-) I have 5000 recipes. I am looking for a way to optimize the list. List shows 50 recipes at a time.

Until now I didn't have LIMIT (I was using Dreamweaver which doesn't optimize well).... Dreamweaver just takes the whole SELECT query (yes, 5000 rows) and writes first 50 and make an <<< >>> previous, next arrows...

Now I am making a list using LIMIT 50, speed is not much faster, but I guess I am making less data flow from the mysql to the server because there are only 50 rows flowing around, as much as I need.

But I have to make a previous and next list, so I have to know how much records there are... I am using now a second SELECT with count(*). Do you have any better idea?

I am using ASP so I can not use the PHP command of reading the number of lines. But I tested that on another page, it is not any faster than my count(*) select...

Is there any way I can get the number of rows and first 50 rows in one query ir is it better to use the 2 queries (read 50 rows from the first and read count of all the rows that match the results from the second). Yes, forgot to mention, I an not take all the recipes that would be simple. But I am using a search engine, so I have to count the results.

But using select with no limit is not the right way I guess (mySQL says to me that I make a lot of next row reads which suggest I make to much whole table scans).



Options: ReplyQuote

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

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.