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);
?>
Member Avatar for iamthwee

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

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

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!

Member Avatar for iamthwee

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

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

commented: good one +14
Member Avatar for iamthwee

yeah sorry I just realised your point.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.