count(*) and count(column_name), what's the difference in mysql.
- performance wise, no difference, however when you specify the column name, you only get a count of rows where that field is not null. Also, count(columnName) just "feels" better in my opinion.Jay– Jay2010-05-20 19:00:33 +00:00Commented May 20, 2010 at 19:00
- 3@Jay: There is a performance difference. See: mysqlperformanceblog.com/2007/04/10/count-vs-countcolMark Byers– Mark Byers2010-05-20 19:54:44 +00:00Commented May 20, 2010 at 19:54
Add a comment |
4 Answers
COUNT(*)counts all rows in the result set (or group if using GROUP BY).COUNT(column_name)only counts those rows wherecolumn_nameis NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable).COUNT(1)is the same asCOUNT(*)since 1 can never be NULL.
To see the difference in the results you can try this little experiment:
CREATE TABLE table1 (x INT NULL); INSERT INTO table1 (x) VALUES (1), (2), (NULL); SELECT COUNT(*) AS a, COUNT(x) AS b, COUNT(1) AS c FROM table1; Result:
a b c 3 2 3
1 Comment
zloctb
COUNT(*) counts not all rows in the result set (or group if using GROUP BY) only not counts those row where all column_name is NOT NULL
Depending on the column definition -i.e if your column allow NULL - you could get different results (and it could be slower with count(column) in some situations as Mark already told).
Comments
There is no performance difference between COUNT (*), COUNT (ColumnName), COUNT (1).
Now, if you have COUNT (ColumnName) then the database has to check if the column has a NULL value, and NULLs are eliminated from aggregates. So COuNT (*) or COUNT (1) is preferable to COUNT (ColumnName) unless you want COUNT (DISTINCT ColumnName)
Comments
In most cases there's little difference, and COUNT(*) or COUNT(1) is generally preferred. However, there's one important situation where you must use COUNT(columnname): outer joins.
If you're performing an outer join from a parent table to a child table, and you want to get zero counts in rows that have no related items in the child table, you have to use COUNT(column in child table). When there's no matches, that column will be NULL, and you'll get the desired zero count (actually, you'll get NULL, but you can convert that to 0 with IFNULL() or COALESCE()). If you use COUNT(*), it counts the row from the parent table, so you'll get a count of 1.
SELECT c.name, COALESCE(COUNT(o.id), 0) AS order_count FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id 