Page 1 of 2
Is there a quicker way?
Posted: Thu Apr 13, 2006 4:47 pm
by Terriator
This is what I currently use to count my total users:
Code: Select all
$result = doquery("SELECT COUNT(*) as TOTALFOUND FROM users",$link);
print (mysql_result($result,0,"TOTALFOUND"));
This script is counting thousands of users, thus it takes a while to load - What is the quickest way to count the total rows of a table??
Posted: Thu Apr 13, 2006 4:56 pm
by feyd
Re: Is there a quicker way?
Posted: Thu Apr 13, 2006 5:15 pm
by AKA Panama Jack
Terriator wrote:This is what I currently use to count my total users:
Code: Select all
$result = doquery("SELECT COUNT(*) as TOTALFOUND FROM users",$link);
print (mysql_result($result,0,"TOTALFOUND"));
This script is counting thousands of users, thus it takes a while to load - What is the quickest way to count the total rows of a table??
Do not count every field in the table. Select only one field and make it an integer field and not a text field.
Example: "SELECT COUNT(user_id) as TOTALFOUND FROM users"
Your query is compiling the total count from all fields and will take longer to process.
Posted: Sat Apr 15, 2006 4:45 am
by Terriator
I've tried my way around, but I simply can't make the describe thing echo just the amount of rows - How do I do this?
Posted: Sat Apr 15, 2006 9:17 am
by feyd
oops, I was thinking of a different query for some reason..
SHOW TABLE STATUS is what you want.
Posted: Mon Apr 17, 2006 4:51 am
by Oren
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.
Thx.
Posted: Mon Apr 17, 2006 11:35 am
by s.dot
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
Posted: Mon Apr 17, 2006 11:53 am
by Oren
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).
Posted: Mon Apr 17, 2006 12:18 pm
by timvw
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.
Posted: Mon Apr 17, 2006 12:51 pm
by Oren
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.
Posted: Mon Apr 17, 2006 1:40 pm
by RobertGonzalez
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.
This is just my opinion.
Posted: Mon Apr 17, 2006 2:12 pm
by Oren
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.
Posted: Mon Apr 17, 2006 2:25 pm
by Christopher
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.
Posted: Mon Apr 17, 2006 2:51 pm
by printf
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!
printf!
Posted: Mon Apr 17, 2006 2:59 pm
by timvw
As i already said, it depends on the context...
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...
Code: Select all
<?php
// +---------------------------------------------------------------------------
// | Author: Tim Van Wassenhove <timvw@users.sourceforge.net>
// +---------------------------------------------------------------------------
ini_set('error_reporting', E_ALL);
ini_set('display_errors', TRUE);
function microtime_float() {
return microtime(true);
//list($usec, $sec) = explode(' ', microtime());
//return ((float)$usec + (float)$sec);
}
function prepare($rows = 100) {
mysql_query('CREATE TABLE testtable (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));');
for ($i = 0; $i < $rows; ++$i) {
mysql_query('INSERT INTO testtable (id) VALUES (NULL)') or die(mysql_error());
}
}
function cleanup() {
mysql_query('DROP TABLE testtable');
}
function benchmark_counts($loops = 1000000) {
$times = array();
for ($i = 0; $i < 2; ++$i) {
$times[$i] = 0;
}
for ($i = 0; $i < $loops; ++$i) {
$j = 0;
$start = microtime_float();
mysql_query('SELECT id FROM testtable') or die(mysql_error());
$result = mysql_query('SELECT COUNT(id) FROM testtable') or die(mysql_error());
mysql_fetch_assoc($result);
$end = microtime_float();
$times[$j++] += ($end - $start);
$start = microtime_float();
$result = mysql_query('SELECT id FROM testtable') or die(mysql_error());
mysql_num_rows($result);
$end = microtime_float();
$times[$j++] += ($end - $start);
}
return $times;
}
function print_times($times) {
for ($i = 0; $i < count($times); ++$i) {
echo 'Results for ' . $i . ': ' . $times[$i] . '<br>' . "\n";
}
}
mysql_connect('localhost', 'username', 'password');
mysql_select_db('test');
$times = array();
$runs = 4;
for ($i = 0; $i < $runs; ++$i) {
prepare(1000);
$times[] = benchmark_counts(pow(10, $i + 1));
cleanup();
}
for ($i = 0; $i < $runs; ++$i) {
echo 'Results for ' . pow(10, $i + 1) . ' loops:' . "\n";
print_times($times[$i]);
echo "\n";
}
?>