0

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);
?>
2
Contributors
8
Replies
18
Views
3 Years
Discussion Span
Last Post by iamthwee
0

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!

1

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

Votes + Comments
good one
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.