Re: [PHP] Fastest way to get table records' number

On Wed, Mar 19, 2008 at 1:19 PM, Andrew Ballard <[email protected]> wrote:
> On Wed, Mar 19, 2008 at 1:04 PM, TG <[email protected]> wrote:
>  >
>  >  It seems that count(*) pulls all the data from the row then performs a count
>  >  increment whereas count(did) only pulls the 'did' column.
>
>  Again, I don't believe COUNT(*) pulls any data. If there is a row, it
>  simply counts it. The row could be full of NULLS (if allowed by your
>  schema - yikes) and it will still be counted. I'd guess that COUNT(1)
>  does the same thing. COUNT(did) does only examine the `did` column,
>  but NULL values are excluded from the count.

    You are correct, sir!  COUNT(*) doesn't look into the data at all,
it just counts all rows.  Keep in mind that COUNT(*) may very well
return a different result than the cardinality of the table, since
COUNT(*) couldn't care less if the row is unique or not.

>  >  I wonder if count(did) is the same speed as count(1) or if it will depend on
>  >  how much/what type of data is in 'did'.
>  >
>  >
>  >  I also wonder why count() takes a parameter.  Isn't it always going to count
>  >  +1 for the row?   I'll have to look that up sometime.
>
>  It takes a parameter because it depends on what you want to count.
>  COUNT(*) will return the number of rows matching the WHERE clause.
>  COUNT(`column_name`) will return the number of non-NULL values in the
>  column `column_name`. You could have a million rows in the table, but
>  if every row has NULL in `column_name`, the COUNT() will return 0.
>  There is also COUNT(DISTINCT `column_name`), which counts the number
>  of distinct, non-NULL values in the column.

    You can extend a SELECT COUNT(*) query almost exactly like you
would a basic SELECT query.  Examples:

    SELECT COUNT(*) FROM users WHERE username LIKE '%dan%';
    SELECT COUNT(DISTINCT color) FROM products;
    SELECT COUNT(*) FROM table LIMIT 0,1;

    Any limits or the like on the query (such as in the last example)
will pretty much be ignored, though, because COUNT(*) only returns the
number of matching rows, not any other data whatsoever.

-- 
</Daniel P. Brown>
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283