Is there a quicker way?

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!

Moderator: General Moderators

Terriator
Forum Commoner
Posts: 60
Joined: Mon Jul 04, 2005 12:46 pm

Is there a quicker way?

Post 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??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Re: Is there a quicker way?

Post 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.
Terriator
Forum Commoner
Posts: 60
Joined: Mon Jul 04, 2005 12:46 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

oops, I was thinking of a different query for some reason.. SHOW TABLE STATUS is what you want.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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 :wink: (the table had already contained about 600k so together it's more than 10M rows).
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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";
}
?>
Post Reply