What is the most efficient way to get the number of rows from a table?

[dawbin]

What is the most efficient way to get the number of rows from a table? I'm able to do it with the following code, but looping through a recordset of one row seems inefficient. Is there a better way to do this?

<?php
    $con=mysqli_connect("0000000000","0000000000","0000000000","0000000000");
    $sql = 'SELECT count(*) AS t FROM count';
    $result = mysqli_query($con,$sql);
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['t']';
    }
    mysqli_close($con);
?>

[iamthwee]

Do you just want the integer returned (row count) or actual values 't'?

[dawbin]

I just need the total rows, so either is fine.

[iamthwee]

http://uk3.php.net/mysql_num_rows

You wouldn't need the while loop.

[dawbin]

The code from that example is simpler for sure, but the database engine is processing every column of every row only to determine the number of rows.

I benchmarked each strategy and the more cumbersome version appears faster for larger tables.

VERSION #1 Slower:

    $link = mysql_connect("", "", "");
    mysql_select_db("", $link);
    $start=microtime(); 
    $result = mysql_query("SELECT * FROM tax", $link);
    $num_rows = mysql_num_rows($result);
    echo "$num_rows Rows in " . (microtime()-$start) . " milliseconds.";        mysql_close($link);

82866 Rows in 0.56819 milliseconds.

VERSION #2 Faster:

    $con=mysqli_connect("","","","");
    $start=microtime(); 
    $sql = 'SELECT count(*) AS t FROM tax';
    $result = mysqli_query($con,$sql);
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['t'] . ' Rows in ' . (microtime()-$start) . ' milliseconds.' ;
    }
    mysqli_close($con);

82866 Rows in 0.01545 milliseconds. (mysqli_fetch_assoc)

I appreciate your help and giving me an alternate strategy to test against!

[iamthwee]

Why have you put (microtime()-$start) line 6 inside the while loop shouldn't it come at the end?

[iamthwee]

Looks like you may have a point with count() being faster though.

http://stackoverflow.com/questions/2485224/sql-php-which-is-faster-mysql-num-rows-or-select-count

Just use it as shown in the eg above.

[dawbin]

Only one row is returned with "select count(*)", but I could have used break to be more explicit.

[iamthwee]

yeah sorry I just realised your point.