Re: Make optimized LEFT JOIN with GROUP BY
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).
Yours
Jerry