mysql_num_rows vs. count

[skiflyer]

Asking anyone who might know a little bit about the details of both mysql and php's implementations...

Is there a significant difference from running a

$result = mysql_query("SELECT count(somecolumn) WHERE 1")
$row = mysql_fetch...
echo $row[0];

as compared to
$result = mysql_query("SELECT somecolumn) WHERE 1");
echo mysql_num_rows($result);

-Rob

 

[sleipnir214]

Just from which MySQL subsystem you are retrieving the value. And query efficiency, too, I suppose.

In every SELECT query, MySQL provides not only the data to be fetched, but also query meta-information. mysql_num_rows() merely provides you access to that meta-information. If you use "...count(...)...", then you are getting the information from MySQL database engine explicitly.

But here's the "efficiency" thing. Use of count() is a brute-force process in MySQL, and makes poor use of indeces. If the number of rows is large, count() can slow queries.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Karl Blessing]

With MySQL processing load aside. Evaluate the type of information you would like to know. Do you ONLY want to know how many rows are there, if so using count() will only send back one record to the PHP script. Otherwise using num results, you get back all the qualifying records to the script ( larger ammount of data ), of which you can count afterwards, but is more useful, if you plan on using that information ( invidisual record information ) in the script itself.

Karl Blessing
PHP/MySQL Developer
Envynews.com

 

[skiflyer]

That's not quite true Karl... not using Count results in the return of a record set, which is a resource id. I don't believe this is the actual information, but rather a way in which to receive it. Should be easy enough to test, run a query, close your database connection and see if you can read your recordset. Then I'm not going to count the information, I'm going to read the metadata for that recordset.

It seems to me that using the php method sounds better, as count is a crappy aggregate function... but I'm not positive on that.

I guess one point in Sleipnir's post which might help me decide is the "mysql subsystem", are you referring to the mysql libraries in PHP, or the engine which is answering from the mySQL server?

-Rob

 

[sleipnir214]

My comments about subsystems are probably only a reflection of my way of imagining the interior workings of the server. Please disregard.

I think kb244 is more right than my original answer. You shouldn't ask MySQL for more than you need. If the result record set is not sent to the client at the time the query is run, the recordset has to be kept somewhere -- that's server resources you're using that you don't need to.

I thought I had remembered an article at MySQL's website that said sum() was more efficient than count(), but I was mistaken. The opposite is true, but the difference is small.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[SirCharles]

mysql_num_rows only gives you the count of the current fetch. So, unless you're done fetching all the rows that match your query, you won't get an actual count of the number of rows that match the query. I believe what I said is correct, but you should always verify with actual code.

 

[sleipnir214]

Apparently, the MySQL libraries report the number of rows returned by counting the rows returned. I had thought the server-provided query metadata, but I was wrong.

The MySQL client library implement two functions for fetching the results of a query: mysql_store_result() and mysql_use_result(). The difference between the two functions is where the actual return data is stored -- mysql_store_result() fetches all the data from the server and stores it in a data structure at the client side, where mysql_use_result() fetches data a little at a time from the server. You can use mysql_num_rows() [the MySQL library function] on a query return from mysql_store_result() immediately; with mysql_use_result(), you have to fetch all the rows before you can know the number of rows returned.

My research indicates that PHP's mysql_query() function uses MySQL's mysql_store_result() and that PHP's mysql_unbuffered_query() implements MySQL's mysql_use_result().

Thus, you should be able to use PHP's mysql_num_rows() immediately after invoking PHP's mysql_query(). My own experience has shown this to be so.

This also sheds some interesting light on PHP internals. If you are expecting a large data return from a query, it might be best to use mysql_unbuffered_query(). Otherwise you might run into PHP or server resource limits.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!