COUNTING total records from many tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

COUNTING total records from many tables

Post by anjanesh »

Any idea which one would be faster or consume less resources ?
Code 1:

Code: Select all

<?php
$Total[01] = mysql_num_rows(mysql_query("SELECT * FROM `tbl01` WHERE $Condition01"));
$Total[02] = mysql_num_rows(mysql_query("SELECT * FROM `tbl02` WHERE $Condition02"));
.
.
.
$Total[10] = mysql_num_rows(mysql_query("SELECT * FROM `tbl10` WHERE $Condition10"));
print_r($Total);
Code 2:

Code: Select all

<?php
$Res_01 = mysql_query("SELECT COUNT(*) FROM `tbl01` WHERE $Condition01"); $Row_01 = mysql_fetch_row($Res_01); $Total[01] = $Row_01[0];
$Res_02 = mysql_query("SELECT COUNT(*) FROM `tbl02` WHERE $Condition02"); $Row_02 = mysql_fetch_row($Res_02); $Total[02] = $Row_02[0];
.
.
.
$Res_10 = mysql_query("SELECT COUNT(*) FROM `tbl10` WHERE $Condition10"); $Row_10 = mysql_fetch_row($Res_10); $Total[10] = $Row_10[0];
print_r($Total);
Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

COUNT() can often be faster and use less resources depending on how you use it. I'd suggest studying the differences between COUNT(*) and COUNT(`fieldReference`) in terms of speed.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I had a feeling that
SELECT COUNT(*) FROM `tbl01` WHERE $Condition01
would be faster but would
SELECT COUNT(`PKey`) FROM `tbl01` WHERE $Condition01
be faster than COUNT(*) ?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Specifying a column in there is counting the non NULL values, and therefore is more costly. I think.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Assuming PKey is the Primary Key and not null - would it still be costly ?
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Code: Select all

mysql> select count(a.id) as count from iris_behaviour_slip as a, iris_students as b where a.studentid = b.id;
+-------------+
| count(a.id) |
+-------------+
|       67336 |
+-------------+
1 row in set (0.34 sec)


mysql> select count(*) as count from iris_behaviour_slip as a, iris_students as b where a.studentid = b.id;
+----------+
| count(*) |
+----------+
|    67336 |
+----------+
1 row in set (0.45 sec)
I had to join the table with something else just so I got a time that was > 0.00secs! a.id is a primary key. So to answer your question it would seem no.
Post Reply