Page 1 of 1

How do I count the number of rows in a database table?

Posted: Sat Jul 17, 2010 12:23 pm
by tom8521
How do I count the number of rows in a table named 'mail' where Status=1?

The answer then needs to be returned to the screen. Thanks!

Re: How do I count the number of rows in a database table?

Posted: Sat Jul 17, 2010 12:40 pm
by liljester
use this query, and print the result in php:

Code: Select all

select count(*) from mail where status = '1';

Re: How do I count the number of rows in a database table?

Posted: Sat Jul 17, 2010 12:43 pm
by oscardog
Or you could use:

Code: Select all

$query = mysql_query("SELECT * FROM mail WHERE status = '1'");
$totalRows = mysql_num_rows($query);

echo $totalRows;

Re: How do I count the number of rows in a database table?

Posted: Sat Jul 17, 2010 1:12 pm
by liljester
the way you suggest is much more inefficent, as the database would return the entire dataset (all the rows) to php. if you only need the number of rows, its best to use the query i suggested.

Re: How do I count the number of rows in a database table?

Posted: Sat Jul 17, 2010 2:26 pm
by oscardog
liljester wrote:the way you suggest is much more inefficent, as the database would return the entire dataset (all the rows) to php. if you only need the number of rows, its best to use the query i suggested.
Well obviously you would only need to retrieve 'status' from the database and not all of it. This would make it as efficient(or close) to your solution. There would be no noticeable difference in load time or performance. Well, not unless you're handling millions of rows.

Re: How do I count the number of rows in a database table?

Posted: Sun Jul 18, 2010 3:12 pm
by liljester
oscardog wrote:
liljester wrote:the way you suggest is much more inefficent, as the database would return the entire dataset (all the rows) to php. if you only need the number of rows, its best to use the query i suggested.
Well obviously you would only need to retrieve 'status' from the database and not all of it. This would make it as efficient(or close) to your solution. There would be no noticeable difference in load time or performance. Well, not unless you're handling millions of rows.
obviously not, as the original poster didnt know that, and you certainly did not put it in your post. also i would like to point out that the tests ive run it was no where near as efficient to do it either way you suggested. i created a simple table with 3 columns: id (int primary key auto_increment), hash (varchar(32)), status (int). i randomly generated 5000 rows, 2477 of wich had a status = '1'. i ran 1000 queries of each type and took an average of the time and measured approx. memory usage. here are the results:

select count(*) from table01 where status = '1';
1000 queries, average time of: 0.000068 sec
memory usage: 2,400 bytes

select status from table01 wehre status = '1';
1000 queries, average time of: 0.000260
memory usage: 3,464 bytes

select * from table01 where status = '1';
1000 queries, average time of: 0.000439
memory usage: 4,536 bytes

while it is a simplistic test, it proves my original statement that the way you suggest is much more inefficent. your original suggestion (select * from mail where status = '1') executes in 646% of the time and uses 89% more the memory of the method i suggested. your second "obvious" suggestion executes in 382% of the time and uses 44% more memory. so while it may not seem like much longer, it is still much more inefficient, and every little bit counts in programming.


my test script:

Code: Select all

<?php

$db_info = array (
	'host'		=> 'localhost',
	'database'	=> 'test',
	'username' 	=> 'user',
	'password'	=> 'pass!',
);

$iterations = 1000;


$db = mysql_connect($db_info['host'], $db_info['username'], $db_info['password']);
mysql_select_db($db_info['database'], $db);


/*
for($i = 0; $i < 5000; $i++) {
	$hash = md5(mt_rand(0, 1000000));
	$status = mt_rand(0,1);
	$query = "insert into table01 (id, hash, status) values ('', '$hash', '$status');";
	mysql_query($query);
	print mysql_error();
}

print"done.";
*/
$base_mem = memory_get_usage();
for($a = 0; $a < $iterations; $a++) {
	$a_start = microtime(true);
	$query = "select count(*) as cnt from table01 where status = '1';";
	$result = mysql_query($query);
	$row = mysql_fetch_assoc($result);
	print $row['cnt'] ."<br />\n";
	$a_time += number_format((microtime(true) - $a_start), 6);
}
$a_mem = memory_get_usage();
$a_average = number_format(($a_time / $iterations), 6);

for($b = 0; $b < $iterations; $b++) {
	$b_start = microtime(true);
	$query = "select status from table01 where status = '1';";
	$result = mysql_query($query);
	print mysql_num_rows($result) ."<br />\n";
	$b_time += number_format((microtime(true) - $b_start), 6);
}
$b_mem = memory_get_usage();
$b_average = number_format(($b_time / $iterations), 6);

for($c = 0; $c < $iterations; $c++) {
	$c_start = microtime(true);
	$query = "select * from table01 where status = '1';";
	$result = mysql_query($query);
	print mysql_num_rows($result) ."<br />\n";	
	$c_time += number_format((microtime(true) - $c_start), 6);
}
$c_mem = memory_get_usage();
$c_average = number_format(($c_time / $iterations), 6);


print"base memory: $base_mem<br />\n";
print"A: $a_average sec, ". ($a_mem - $base_mem) ." bytes<br />\n";
print"B: $b_average sec, ". ($b_mem - $base_mem) ." bytes<br />\n";
print"C: $c_average sec, ". ($c_mem - $base_mem) ." bytes<br />\n";


?>