5 messages in com.mysql.lists.mysqlRe: Count the number of specific rows| From | Sent On | Attachments |
|---|---|---|
| Servers24 Network | 29 Dec 2006 06:02 | |
| Duncan Hill | 29 Dec 2006 06:57 | |
| Philip Mather | 29 Dec 2006 07:07 | |
| Peter Brawley | 29 Dec 2006 07:21 | |
| Philip Mather | 29 Dec 2006 08:44 |
| Subject: | Re: Count the number of specific rows |
|---|---|
| From: | Philip Mather (phi...@cix.co.uk) |
| Date: | 12/29/2006 07:07:57 AM |
| List: | com.mysql.lists.mysql |
Servers24,
Well this question may seem funny...
No, a funny question would start something like "Why did the nun cross the road?". ;^)
The problem is with counting a user's contribution in my site. Suppose that each user that send an email will be stored in DB. Now I want to count number of times that a user has sent an email. I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if
Get MySQL to do the counting... "SELECT COUNT(*) FROM sent WHERE member_id = ...;" ...saying COUNT(*) is certainly no slower than saying COUNT(id) and the COUNT function is certainly faster than iterating over the result set using whatever language, also make sure you have indexed the member_id field as well. Alternatively you maybe able to incorporate a **|SQL_CALC_FOUND_ROWS |**into an existing query and then do a **| |**|FOUND_ROWS()| to optimize things (see http://dev.mysql.com/doc/refman/4.1/en/information-functions.html) or failing that run a nightly query to compile the stats into a new table containing just member_id and emails_contributed fields? Those would be the standard options.
Regards, Phil