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 SQL_CALC_FOUND_ROWS and 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)
Pop 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?
10 Dec at 9:47 pm
Oh GAWD! Please don’t! The amount of times I typed that in, the last few weeks (pagination purposes) *runs away screaming*
11 Dec at 5:00 am
$sql = "SELECT `something`, COUNT( `index_id` ) AS num_something FROM `wherever`;";// ye olde php query
$num = mysql_num_rows( mysql_query( $sql ) );
// ... or $r->num_something
$r = mysql_fetch_object( mysql_query( $sql ) );
… though I’m not sure about the “optimization” on either (and honestly, unless you’re going through millions of rows in a multi-gigabyte database, it doesn’t much matter… or you don’t have an index, that one hurts too).
The only thing I wouldn’t do is
COUNT( * ), because you’re not ever supposed to*anything…11 Dec at 8:33 am
I know this has nothing whatsoever to do with the blog *snorts* but in every browser I’ve ever visited your site in, <code> text is very, very tiny:
http://i33.tinypic.com/23u2c5i.jpg
(…Although I’ve never tried it in Firefox, because for some reason half of the websites I visit on it show up as either 1) nothingness 2)font sizes screwed up or 3)lots of random special characters, and your site shows up with font sizes of like 5pt :P)
11 Dec at 9:04 am
@Dee: the point is that
"SELECT `something`, COUNT( `index_id` ) AS num_something FROM `wherever`;"doesn’t return the required result when you’re using LIMIT for pagination – which is why the second query is needed.COUNT(*) on MyISAM is fine – it keeps an internal row count.
11 Dec at 12:37 pm
Arjen touched on this a while ago on his blog.
To quote him:
COUNT(col) which actually counts all not-NULL values of col in the result set. So here, the server needs to iterate through all the rows, tallying for which rows col has a not NULL value. Of course, if the col is NOT NULL the server may be able to optimise this, but I’m not sure – after all it’s a result set not a base table.
http://arjen-lentz.livejournal.com/101418.html
I meant to have a chat about using it with an innoDB table when i was at his training day, agh.. me and my shitty memory.
11 Dec at 1:42 pm
I’ve been looking for something like this! Thanks, Jem!