Page 1 of 1

COUNTING total records from many tables

Posted: Wed Mar 08, 2006 10:47 pm
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

Posted: Wed Mar 08, 2006 11:08 pm
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.

Posted: Wed Mar 08, 2006 11:12 pm
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(*) ?

Posted: Wed Mar 08, 2006 11:14 pm
by josh
Specifying a column in there is counting the non NULL values, and therefore is more costly. I think.

Posted: Wed Mar 08, 2006 11:20 pm
by anjanesh
Assuming PKey is the Primary Key and not null - would it still be costly ?

Posted: Sat Mar 11, 2006 7:22 am
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.