How to retrieve row count in large table?

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

Moderator: General Moderators

User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

How to retrieve row count in large table?

Post 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));
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post 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. ;)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post by feyd »

mysql_free_result() tells MySQL you are done with that result set. PHP doesn't care, as it does garbage collection automatically.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post by feyd »

yes.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
Last edited by raghavan20 on Thu Feb 09, 2006 4:47 am, edited 1 time in total.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

testing on a table with only 6 rows isn't the most accurate test
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

No
Because count(field) counts only non-null fields, while count(*) doesn't bother to check if the field is null.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 :)
Post Reply