PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
So what is the fastest way to get the numebr of rows in a table? like when I only want to print the number of the members in my community for example?
To anybody who answers: please post the code that does that as well.
feyd: I've followed the link you gave and read this:
The number of rows. Some storage engines, such as MyISAM and ISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
Do you still recommend using SHOW TABLE STATUS? If so, please post the code.
I run count(*) on a table of nearly 5000 users every day... and even with a where clause, and I don't have problems with the speed at all. In fact it's very fast. I haven't timed it but i get entire page loads full of queries in under .02-.03 seconds
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Just in order to test and see if COUNT(column_here) is fast enough I'm currently having a PHP script (actually a simple loop) the is inserting 10M rows into a database (mysql). The script has been running for the last 6 hours and according to my estimate is going to finish all the INSERT queries very soon. After it's done I'll compare between COUNT(column_here) and mysql_num_rows($result_here) and post the times of each one.
I've already compared between them with about 600k rows in the table and it took mysql_num_rows($result_here) almost 10 seconds while it took COUNT(column_here) about 0.006 seconds only.
Very soon we will have the results for a table with more than 10M rows (the table had already contained about 600k so together it's more than 10M rows).
The advantage of select count(column [, column]+) is that the resultset exists out exactly one row.. Where with mysql_num_rows your resultset exists out of all those rows..
So an accurate test would compare the difference between a query that selects an average of rows that will be available in a resultset... Eg: with pagination there are typically only X rows in a resultset... Now compare select x + select count (x) to select x + mysql_numrows.
Ok, I've got the results.
Rows in table: 10,630,006
With mysql_num_rows() it took: 286.33 seconds
With COUNT(column_here) it took: from 0.02 seconds and up to 0.3 seconds
Of course this is based only on few tests but that's enough to see that you should use COUNT(column_here) rather than mysql_num_rows() for counting the number of rows in a table.
The test also shows that COUNT(column_here) is fast enough and for any web application COUNT(column_here) will do the job.
Oren wrote:The test also shows that COUNT(column_here) is fast enough and for any web application COUNT(column_here) will do the job.
This a rather broad assumption. Your test didn't take into consideration server load, db connections, server processing speeds and a host of other criteria. I agree that the MySQL COUNT() function is a much better alternative to mysql_num_rows(), but that is because of the nature of the two functions. To make the statement you made without qualifying it a little more is a bit dangerous to users that are looking for information related to what you tested.
Ohh no... My test results don't say that COUNT() is the fastest way in the world but they say that COUNT() is way faster than mysql_num_rows().
This isn't math if you forget and we cannot perform an unlimited number of test. An unlimited number of tests is not even needed with such clear and obvious results: 0.3 seconds at max for COUNT() while about 289 seconds for mysql_num_rows(). With such results, I believe that with any combination of OS, PHP version, PHP settings etc COUNT() will still be way faster. Again, this is not math here and we can make conclusions based on several test only - espcially when the results are so obvious.
There have been many discussions about COUNT(*). It is my understanding that, though it is not always the absolute fastest, that is most often the fastest because it allows the database engine to find what it considers the best was to get the result. Usually when something else is faster it is because the DBA knows a quirk of the database.
arborint wrote:There have been many discussions about COUNT(*). It is my understanding that, though it is not always the absolute fastest, that is most often the fastest because it allows the database engine to find what it considers the best was to get the result. Usually when something else is faster it is because the DBA knows a quirk of the database.
When wouldn't be the fastest? I don't see where anything would ever be faster for getting a tables row count. I remember this was brought up in MySQL workshop and one of the developers said something to the effect, don't waste you time on trying to find a faster way to count a table, because there isn't! That was the end of that discussion!
return (1) value, instead of returning millions of values to get that (1) value!
Suppose you've already performed a select, and now you want count those rows..
My experiments show me that the combination of select and mysql_num_rows is faster than select and select count in that case...