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 make 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 hate 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 do nothing against it, as long as you need paging. There are just a few tricks that may reduce your server load a bit.
A fast query that uses good indexes is just faster, no matter where you use it. But on paging where you might calculate thousands of lines it really does matter a lot.
Avoid ORDER BY and GROUP BY because you have to do this (depending on index use) on every line in your table, the limit can’t help you here.
Cache it! But still the query has to be run once, especially for fulltext searches, even the first run of a query might put a lot of load on your servers.
YAGNI (You aint gone need it!)
Nobody will ever see page 300, so why you don’t set an upper limit for you paging. Your user will not mind and I’m sure the bots will find another way to your stuff. If you really care about SEO, you probably can do a separate list for bots that perform with less operations on the database (remove unneeded joins, don’t sort it).
Don’t join tables you don’t need, sure that might sound obvious, but I’ve seen this too many times to not mention this here.
Count is evil
Never, never use SQL_CALC_FOUND_ROWS it’s just equally slow then “SELECT COUNT(*) FROM table”, but you have to do it on every page. The count(*) variant you can cache at last, so you don’t have to do it on every page.
And there’s even another way to avoid the count on paging. It’s the way Facebook does paging on some places. Facebook don’t give you the usually list of pages from 1 to n there, were you can click at any page. They just give you the page before, the current page and the next page, if there’s one. On the application side it’s very easy to find out if you have a page before the current, when you are on the second page there’s one before (so no surprise here). But what about the next page if you don’t want to make a count. Easy stuff, let me predict you display 10 items per page, so query 11 items instead of 10 per page. This one extra item will cost you nearly nothing and now you can count the returned rows in your application. If you have more than ten rows you have a next page and you can happily throw number eleven away.