Background: I wrote this post last week, after discovering what I thought was a nifty trick. Shortly afterwards, I found contradicting evidence that suggested the traditional route of using a separate query of COUNT(*) was faster, and/or it depends on your individual database design. Instead of deleting the post, I figured I should publish it anyway, along with a link to the article that I read, so that those who give a crap about this sort of thing can make their own mind up.
One of the benefits of working on and releasing rev.iew.me — apart from giving me an active place to review (I typed rev.iew first, damnit) — was the excuse it gave me to learn more stuff. I like learning stuff.
Here’s a nifty little MySQL ‘trick’ that I discovered, that helps cut down on unnecessary bloat. When doing pagination, I’d always create the query to deliver the results and a second query to COUNT() all rows, and this adds extra load time. Using
FOUND_ROWS(), we still have two queries but the second is faster because
because the result set need not be sent to the client (source)
SQL_CALC_FOUND_ROWS in to your query just after the
SELECT, and do another query directly afterwards using
SELECT FOUND_ROWS(); E.g.
$query = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM `members` LIMIT 0, 15"); $total = mysql_query("SELECT FOUND_ROWS();");
Assuming there’s 50 members in the members table, we now have the result set for 15 members in $query and the total row count in $total (use mysql_result() to access the number directly). Nifty, huh?