Stumbled across this interesting article at Leo Büttiker’s blog which tells why pagination on a web app is a database killer:

On the view of your database the worst thing you can do in your web app is paging. Paging is horrible in the view of performance. To explain let me take a little example:

SELECT SQL_CALC_FOUND_ROWS gb.*,
u.username,
u.uid,
u.geschlecht,
u.mitfoto,
[... some more fields...]
FROM member_gold_guestbook gb
LEFT JOIN users u ON u.uid=gb.uid_from
[... some more left joins...]
WHERE gb.uid_to=’22152′
AND visible=’1′
LIMIT 0,10;

That’s not that bad at all, but when you go to page 300 your database server will hat you for this. The database server has not only to calculate the 10 items you want to show but also all 3000 previous items.

Sure you may argue nobody will ever go to page 300. Somebody will not, but “googlebot” and his evil brothers will. And the bad thing is that you can, as long as you need paging, nothing do against it. There are just a few tricks that may reduce your server load a bit.

Read the whole article at: @leo’s :: Why your database says paging sucks!