Recently I was asked to develop a website that had a product page listing of over over 20.000 products. The SELECT query to get the products was optimized as much as it could be. But it was not enough! Due to the high volume of traffic this product listings have the server was always with load over 2 (Cent OS using top). I was forced to use a simple optimization that in the end came out to be quite effective.
To get the product listing there were 2 queries. If we simplify them they would look something like this:
SELECT COUNT(*) FROM products WHERE .....
SELECT something FROM products WHERE ..... LIMIT start, offset;
So even though the COUNT(*) in MySQL goes pretty fast it uses a lot of memory. This COUNT(*) was needed to find out how many products were there and display the number of pages.
So instead of having this 1,2,3,4… page numeration I got it down to prev/next
Here comes the trick. The first 1,2,3,4… navigation requires 2 SQL queries to be done. The second one requires ONLY ONE.
The trick is to select one more result than you are actually showing. Let’s say that you want to display 20 results per page, you select 21. If the 21st record exists than you have a “next page” if it doesn’t you don’t.
What about the “previous page” link?
The LIMIT start, offset in the SQL query will tell you if you have a “previous page” if this number is different from 0 (start !=0) or to be more specific
if(($start - $records_per_page) <= 0)
you have a previous page.
This technique is useful for high volume traffic sites where every hardware resource matters. It can not be applied on all listings due to design/functionality issues, but in some cases it could be a life saver.