Counting members

[DeNasio]

Hello,

I have a table called "Members" in which I store the information of all the members of my site. Is there an easy way to count the total members in a table?

[freddydoesphp]

Use COUNT()

$result = mysql_query("select COUNT(idfield) as totalnum from members");
$totalnum = mysql_result($result, 0); 

[magicscripts]

the way I do is is like this:

$result = mysql_query("SELECT * FROM users ORDER BY user_id DESC");
$num_rows = mysql_num_rows ($result);

echo "There are $num_rows members!"; 

$num_rows is the number of members in the database. And of course you use that after you connect to the database.

[freddydoesphp]

magicscripts, the only problem I have with that code is, why would you want to pull all the records and all the fields for each record just to get the total number of records. YOu are pulling way more information than you need to. When you use COUNT() you are only returning one record with one field in it. So let's say your table has 15 fields and the table has 10,000 records in it. So you would need to retrieve all 10,000 records just to get a count. That is not very efficient.

This is exactly what COUNT() was designed for, an efficient method to get the number of records in a table.

[DR_LaRRY_PEpPeR]

Originally posted by freddydoesphp
Use COUNT()

$result = mysql_query("select COUNT(idfield) as totalnum from members");
$totalnum = mysql_result($result, 0); 

he could probably use COUNT(*) which is faster than COUNT(idfield), right? i assume since he wants to count the members he wants to count the total rows in the table and not rows with a non NULL idfield (which is what COUNT(idfield) is doing).

[freddydoesphp]

By idfield I meant whatever non null unique key field he is using in his table, in which case COUNT(idfield) would be faster than COUNT(*) because COUNT(*) still needs to grab all fields for the record, right?

[DR_LaRRY_PEpPeR]

Originally posted by freddydoesphp
By idfield I meant whatever non null unique key field he is using in his table, in which case COUNT(idfield) would be faster than COUNT(*) because COUNT(*) still needs to grab all fields for the record, right?

no, COUNT(*) is optimized to return very fast. AFAIK it doesn't even look at the rows. but if you give it a column to COUNT it then has to look at every row to see if any rows are NULL. it's stated in the manual somewhere...

[DR_LaRRY_PEpPeR]

here ya go: www.mysql.com/doc/G/r/Group_by_functions.html

[freddydoesphp]

Interesting, that is some excellent bit of knowledge, and something I did not know, thanks. BTW I ran some benchmark tests against the theory and sure enough COUNT(*) was faster. Here are the results.

COUNT(*)

Server Software:        Apache/1.3.12                                      
Server Port:            80

Concurrency Level:      100
Time taken for tests:   4.903 seconds
Complete requests:      2000
Failed requests:        0
Total transferred:      1116824 bytes
HTML transferred:       613438 bytes
Requests per second:    407.91
Transfer rate:          227.78 kb/s received

Connnection Times (ms)
              min   avg   max
Connect:        0    22   114
Processing:    57   198  3005
Total:         57   220  3119

COUNT(idfield)

Server Software:        Apache/1.3.12                                      
Server Port:            80

Concurrency Level:      100
Time taken for tests:   5.287 seconds
Complete requests:      2000
Failed requests:        0
Total transferred:      1119960 bytes
HTML transferred:       616080 bytes
Requests per second:    378.29
Transfer rate:          211.83 kb/s received

Connnection Times (ms)
              min   avg   max
Connect:        0    22   106
Processing:    69   211  3054
Total:         69   233  3160

[Abstraction]

Now, why is it faster?

[freddydoesphp]

Did you read the link Dr. Larry Pepper posted above, it tells you why.

From mysql.
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

I think its because it doesn't care if any fields are null or not it just counts rows, where count(idfield) would search for null fields along the way. I think. Where is MattR when we need him.

[DR_LaRRY_PEpPeR]

Originally posted by freddydoesphp
I think its because it doesn't care if any fields are null or not it just counts rows, where count(idfield) would search for null fields along the way. I think. Where is MattR when we need him.

that sounds right to me. i don't even know if it looks at the rows (for COUNT(*) i mean). i thought maybe it keeps the track of the number of rows somewhere. maybe the index file perhaps?

and thanks for the benchmarks! it's cool to see proof.