[X] My Assistant

[Master Jake]

I'm still learning PHP, obviously, and would consider myself around the intermediate level. I don't want to anger anyone by making them think that I think that I'm an expert in PHP programming. I just had a question about a couple debatable methods and wanted to get the options of others.

The question here is, which method do you find more efficient quick in the situation of needing to count the number of rows returned from a mysql_query. I was taught that mysql_num_rows() was a slow, inelegant, and overall bad method of doing this and that MySQL was just the opposite.

What are your opinions?

Example PHP:

CODE

$query = mysql_query("SELECT * FROM `users`");
$count = mysql_num_rows($query);
echo $count;

vs Example MySQL:

CODE

$query = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) FROM `users`"));
echo $query["COUNT(*)"];

[AdaHacker]

You learned right. Of course, it depends on the situation, but SELECT COUNT(*) is normally faster. The reason is simply that using mysql_num_rows(), the database server has to actually build the result set. With a COUNT(*), on the other hand, the DB may be able to determine the number of rows just from indexes, which is much faster. So if all you want is the count, then mysql_num_rows() ends up doing a lot more work for the same result.

Another down side is that mysql_num_rows doesn't work with unbuffered queries. So if you need to work with very large result sets, using mysql_num_rows might not be an option. However, COUNT(*) is always available.

On the other hand, if you need to buffer and actually use the results of the query anyway, then I imagine there isn't much difference. Though personally, I'm in the habit of just always using SELECT COUNT(*), since it always works and is normally pretty fast.

[Master Jake]

Thanks for the reply.

I was thinking: couldn't you do this if you wanted to select the count AND the actual values for use in one query?

CODE

SELECT *, COUNT(*) FROM `users`

[AdaHacker]

I was thinking: couldn't you do this if you wanted to select the count AND the actual values for use in one query?

No, you couldn't. At least, not like that. You can't mix aggregate functions like COUNT() with non-aggregated columns. SQL doesn't work that way.

You could do something like you've posted in standard SQL by adding in a scalar subquery to do the count:

SQL

SELECT *, (SELECT COUNT(*) FROM `users`) AS cnt FROM `users`

However, that would be absolutely terrible idea. Not only is it ugly, but it would kill performance, as the count query would be run once for every row in the table.

However, MySQL does have an extension for that sort of thing. You can include SQL_CALC_FOUND_ROWS in the query to calculate the number, and then follow it up with a call to FOUND_ROWS() to return the number, like this:

CODE

SELECT SQL_CALC_FOUND_ROWS * FROM `users`;
SELECT FOUND_ROWS();

Of course, while it may seem better to do it in one query, that's not necessarily any faster. With proper indexing, the separate SELECT COUNT(*) can still beat it out.

[Master Jake]

Ooooh, I used SQL_CALC and FOUND_ROWS in my awesome paging system that displays the page the user is currently on and up to 3 adjacent pages with links, back and next buttons, etc.

=D I am proud of it.

[nightscream]

I also have a question, I'm not that good in MySQL, I mean don't really know the advanced stuff like above, never heard of it but my question is: is there a difference between COUNT(*) AND COUNT(`id`), id is the primary key. I'm talking about speed btw

[RPGonzo]

testing that in a table with 5000+ rows both were about the same, for me

Execution Time : 00:00:00:016 ( average )

but that's a VERY crude test at best through SQLyog ... and could probably be ruled null because of fluctuations in network traffic and so forth ... but thought i would post what i saw in that test

[AdaHacker]

is there a difference between COUNT(*) AND COUNT(`id`), id is the primary key. I'm talking about speed btw

Yes there can be a speed difference. It depends on the column you're counting on. This is because COUNT(*) and COUNT(id) are not semantically equivalent - COUNT(*) will return the total number of rows in the table, while COUNT(id) will only count the number of rows where that column is not null. So if your id column is not nullable (which it probably isn't since 'id' is usually the primary key), then the performance should be about the same. However, if you use a nullable column for the count, it's going to be slower.

[RudiVisser]

Actually the fastest method of getting a row count is to use SHOW TABLE STATUS LIKE `tablename` and take the Rows column.

For InnoDB this is only approximate, this will only affect you when there's ALOT (talking >15000) rows. But when it comes to counting that many rows with COUNT(*), you're going to take a huge performance hit.

[AdaHacker]

For InnoDB this is only approximate, this will only affect you when there's ALOT (talking >15000) rows. But when it comes to counting that many rows with COUNT(*), you're going to take a huge performance hit.

Either that's a typo or you're setting your bar for "a lot of rows" a little low. Unless you're running your DB server on a pocket calculator, a COUNT(*) should never be slow on a table of only 15,000 rows. It's true that if the DB does the count by performing an index scan, then it will get slower as the table grows, but you'd need a lot more data. Maybe with 15 million rows and decent server load you'd see issues, but 15,000 rows is less than nothing. If you're having performance problems with a table that small, there's probably something wrong with your server.

[RudiVisser]

Sorry I didn't mean a performance hit, I meant a performance difference (between COUNT(*) and SHOW TABLE STATUS).

But you're right, add a 0 or 2 and you'll start to see the difference.

[nightscream]

Ok thank you, this was helpfull
btw how do you guys come up with this? just test the speed?