Re: Fastest way to get table records' number

On Wed, Mar 19, 2008 at 1:19 PM, Andrew Ballard <aballard@gmail.com> wrote: > On Wed, Mar 19, 2008 at 1:04 PM, TG <tg-php@gryffyndevelopment.com> 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