Page 1 of 2
How to retrieve row count in large table?
Posted: Wed Feb 08, 2006 11:21 pm
by Benjamin
I have a very large table with over 1,300,000 entries.
I need a very fast method of obtaining the number of rows in the table. The table is indexed and highly optmized but the following code still takes about 5 to 10 seconds to return a result.
I noticed that phpMyAdmin can return the number of rows pretty much instantly so I am assuming there is a faster way to do it.
Code: Select all
$Rows = mysql_num_rows(mysql_query("select `field` from `table`", $Connect));
Posted: Wed Feb 08, 2006 11:25 pm
by josh
Code: Select all
$result=mysql_query("select count(*) from table");
$count = mysql_result($result,0,0);
mysql_free_result();
is how phpmyadmin does it
Posted: Wed Feb 08, 2006 11:38 pm
by feyd
Although indirectly what phpMyAdmin does, that I remember is not exaclty how phpMyAdmin does it. phpMyAdmin reads multiple values from the table properties, one of which is number of records.
http://dev.mysql.com/doc/refman/4.1/en/ ... tatus.html may be of interest.
Posted: Wed Feb 08, 2006 11:41 pm
by Benjamin
Thank you, the speed difference is quite significant.
One question though, is it necessary to call mysql_free_result();? I am assuming that only an integer would be in memory after the count(*) query. Am I wrong? Does the $result resource contain more than that?
Posted: Wed Feb 08, 2006 11:46 pm
by josh
Call mysql_free_result() as soon as possible in every one of your scripts, in this case yes the memory being used is trivial but it is principle.
Feyd, that makes sense that phpmyadmin would go that route, but count() makes sense in this situation -
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.
Posted: Thu Feb 09, 2006 12:00 am
by feyd
yeah.. I only remember the status check.. but they may have hidden the count check further in the code.. it was a while ago that I last read through the code.

Posted: Thu Feb 09, 2006 12:35 am
by Benjamin
To clarify, if I have a MySQL query inside of a function I should free the memory before the function returns the result? The Memory is not released automatically?
Also, a lot of times I re-use the $result variable. Would that free the memory being used by the previous query?
Posted: Thu Feb 09, 2006 12:47 am
by feyd
mysql_free_result() tells MySQL you are done with that result set. PHP doesn't care, as it does garbage collection automatically.
Posted: Thu Feb 09, 2006 12:51 am
by Benjamin
Oh ok, so the sole purpose of that is to help the MySQL database run more efficently? So then I really should use mysql_free_result() as much as possible, especially on high traffic sites. Is that right?
Posted: Thu Feb 09, 2006 12:56 am
by feyd
yes.
Posted: Thu Feb 09, 2006 4:45 am
by raghavan20
jshpro2 wrote:Code: Select all
$result=mysql_query("select count(*) from table");
$count = mysql_result($result,0,0);
mysql_free_result();
is how phpmyadmin does it
is count(onesomefield) faster than running count(*) ?
Code: Select all
$result=mysql_query("select count(`someUniqueId`) from table");
$count = mysql_result($result,0,0);
mysql_free_result();
EDIT: I found the result for the above question...
Code: Select all
mysql> select * from test3;
+-----------+------+
| company | name |
+-----------+------+
| google | gg |
| yahoo | yh |
| microsoft | msft |
| ibm | ibm |
| intel | in |
| accenture | acc |
+-----------+------+
6 rows in set (0.45 sec)
mysql> select count(*) from test3;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)
mysql> select count(company) from test3;
+----------------+
| count(company) |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)
Running count on a single field is faster...
Posted: Thu Feb 09, 2006 4:47 am
by JayBird
raghavan20 wrote:[is count(onesomefield) faster than running count(*) ?
Code: Select all
$result=mysql_query("select count(`someUniqueId`) from table");
$count = mysql_result($result,0,0);
mysql_free_result();
No
Posted: Thu Feb 09, 2006 5:02 am
by JayBird
testing on a table with only 6 rows isn't the most accurate test
Posted: Thu Feb 09, 2006 5:03 am
by Weirdan
No
Because count(field) counts only non-null fields, while count(*) doesn't bother to check if the field is null.
Posted: Thu Feb 09, 2006 5:04 am
by Weirdan
testing on a table with only 6 rows isn't the most accurate test
as well as testing without purging the caches between test runs
