MySQL performance tip: Watch out for pagination queries
Applications that paginate tend to bring the server to its knees. In showing you a page of results, with a link to go to the next page, these applications typically group and sort in ways that can't use indexes, and they employ a
Optimizations can often be found in the user interface itself. Instead of showing the exact number of pages in the results and links to each page individually, you can just show a link to the next page. You can also prevent people from going to pages too far from the first page.
On the query side, instead of using
Refer - http://www.infoworld.com/d/data-management/10-essential-performance-tips-mysql-192815?page=0,1
Applications that paginate tend to bring the server to its knees. In showing you a page of results, with a link to go to the next page, these applications typically group and sort in ways that can't use indexes, and they employ a
LIMIT and offset that causes the server to do a lot of work generating, then discarding rows. Optimizations can often be found in the user interface itself. Instead of showing the exact number of pages in the results and links to each page individually, you can just show a link to the next page. You can also prevent people from going to pages too far from the first page.
On the query side, instead of using
LIMIT with offset,
you can select one more row than you need, and when the user clicks the
"next page" link, you can designate that final row as the starting
point for the next set of results. For example, if the user viewed a
page with rows 101 through 120, you would select row 121 as well; to
render the next page, you'd query the server for rows greater than or
equal to 121, limit 21.Refer - http://www.infoworld.com/d/data-management/10-essential-performance-tips-mysql-192815?page=0,1
