Member Avatar for 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);
?>
  • 2 Contributors
  • 8 Replies
  • 241 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Member #46692

Dani AI

Generated

Good experiment by and useful pointer from — the short answer is: pick the method that matches your storage engine and how fresh the number must be.

A few practical points missing from the thread:

  • Storage engine matters. Some engines (historically MyISAM) keep a quick-to-read row count in metadata; others (InnoDB) do not, so a server-side COUNT requires scanning index entries or rows. That means a perfectly fine COUNT(*) can still be expensive on InnoDB for very large tables.
  • If an exact, always-up-to-date count is required and you run it frequently, maintain a single-row counter (updated atomically on INSERT/DELETE) or a small summary table that your application increments/decrements. That avoids repeated full scans but requires careful transactional handling to stay correct under concurrency.
  • If an approximate value is acceptable (dashboards, statistics), read the table estimate from information_schema or SHOW TABLE STATUS; it is fast but not always precise for transactional engines.

Quick optimizations and checks:

  • To test whether the table has any rows, use a LIMIT 1 existence query rather than a full COUNT.
  • When counting with conditions, make sure the WHERE clause can use an index; counting an indexed column is much cheaper than scanning full row data.
  • For short, idiomatic PHP with PDO:
    $count = $pdo->query('SELECT COUNT(*) FROM my_table')->fetchColumn();

    For a maintained counter pattern:

    UPDATE row_counts SET cnt = cnt + 1 WHERE name = 'my_table';
SELECT cnt FROM row_counts WHERE name = 'my_table';

Microbenchmarks can be misleading: run multiple iterations, clear caches if you need cold timings, and test with realistic table sizes and concurrency. For most web apps a single COUNT(*) is fine occasionally; for heavy, frequent counting use a cached/maintained counter or summary write pattern.

Recommended Answers

All 8 Replies

Member Avatar for Member #46692

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

Member Avatar for dawbin

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

Member Avatar for 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!

Member Avatar for Member #46692

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

Member Avatar for dawbin

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 Member #46692

yeah sorry I just realised your point.

Edited by Member #46692
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.