You are here:
  1. Home
  2. Blog
  3. Geek
  4. Total Rows In MySQL Query 'Trick'

Total Rows In MySQL Query ‘Trick’

 |  Geek

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 — 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?

Jem Turner +44(0)7521056376

6 comments so far

  1. Dee said:

    $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…

  2. Anita said:

    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:
    (…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)

  3. Jem said:

    @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.

  4. Mat said:

    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.

    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.

Follow on Instagram