Total Rows In MySQL Query ‘Trick’

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?

affiliate window advert

6 comments so far

  1. Vera said:
    On December 10, 2008 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*

  2. Dee said:
    On December 11, 2008 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…

  3. Anita said:
    On December 11, 2008 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)

  4. Jem said:
    On December 11, 2008 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.

    The only thing I wouldn’t do is COUNT( * ), because you’re not ever supposed to * anything…

    COUNT(*) on MyISAM is fine – it keeps an internal row count.

  5. Mat said:
    On December 11, 2008 at 12:37 pm

    Arjen touched on this a while ago on his blog.

    To quote him:
    COUNT(*) counts rows. If the underlying table is MyISAM, and there’s no WHERE clause, then this is extremely fast as MyISAM maintains a row count of the entire table. Otherwise, the server just needs to count the number of rows in the result set. Which is different from….

    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.

  6. Hannah said:
    On December 11, 2008 at 1:42 pm

    I’ve been looking for something like this! Thanks, Jem!