Is mysql_num_rows efficient and/or standard practice?

Question

A while ago I was poking around with SQLite, trying to port some of my sites to use it instead of MySQL. I got hung up on the lack of a function to count results, like PHP's mysql_num_rows(). After searching a little I discovered this mail list, which says (as I understand it) that SQLite doesn't have that functionality because it's inefficient. It states that it is bad form to write code that needs to know how many rows are returned.

I generally use mysql_num_rows to check for empty return results. For example:

$query = "SELECT * FROM table WHERE thing = 'whatever'";
$results = mysql_query($query);

if (mysql_num_rows($results)) {
 while ($row = mysql_fetch_array($results)) {
  echo "<p>$row[whatever]</p>";
 }
} else {
 echo "<p>No results found</p>";
}

The vehement distaste for the concept of mysql_num_rows() in the SQLite community makes me wonder if it's that terribly efficient for regular MySQL in PHP.

Is there a better, more accepted way for checking the size of a MySQL result set in PHP besides mysql_num_rows()?

EDIT: I'm not just using mysql_num_rows to get the count--I would use a COUNT query for that. I'm using it to check if there are any results before outputting everything. This is useful for something like displaying search results - it's not always guaranteed that there will be results. In SQLite world, I have to send one COUNT query, check if there is something, and then send a SELECT query to get everything.

Answer 1

Regardless of whether or not you actually use what you SELECTed, all of the rows are still returned. This is terribly inefficient because you're just throwing away the results, but you're still making your database do all of the work for you. If all you're doing is counting, you're doing all that processing for nothing. Your solution is to simply use COUNT(*). Just swap COUNT(*) in where you would have your SELECT statement and you're good to go.

However, this mostly applies to people using it as a complete substitute for COUNT. In your case, the usage isn't really bad at all. You will just have to manually count them in your loop (this is the preferred solution for SQLite users).

The reason being is in the underlying SQLite API. It doesn't return the whole result set at once, so it has no way of knowing how many results there are.

Answer 2

As explained on the mailing list you found. It is inefficient to return the count of rows because you need to allocate a lot of memory to hold the entire (remaining) result set. What you could do, is to simply use a boolean to test if you have output anything.

$query = "SELECT * FROM table WHERE thing = 'whatever'";
$results = mysql_query($query);

$empty_result = true;
while ($row = mysql_fetch_array($results)) {
    echo "<p>$row[whatever]</p>";
    $empty_result = false;
}

if ($empty_result) {
    echo "<p>No results found</p>";
}

Answer 3

You already have something that is telling you if you've got results in mysql_fetch_array(). It returns false if there are no more rows to fetch (from php.net).

$query = "SELECT * FROM table WHERE thing = 'whatever'";
$results = mysql_query($query);
if($results) {
    $row = mysql_fetch_array($results);
    if($row) {
        do {
            echo "<p>{$row[whatever]}</p>";
        } while($row = mysql_fetch_array($results));
    } else {
        echo "<p>No results found</p>";
    }

} else {
    echo "<p>There was an error executing this query.</p>";
}