MySQL COUNT: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

COUNT has three common forms, and each answers a different question.

query.sql

SQL

CREATE TABLE signups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(40) NOT NULL,
    referrer VARCHAR(20) NULL,
    plan VARCHAR(10) NOT NULL
);
INSERT INTO signups (email, referrer, plan) VALUES
    ('a@example.com', 'google', 'free'),
    ('b@example.com', NULL,     'pro'),
    ('c@example.com', 'google', 'free'),
    ('d@example.com', 'friend', 'pro'),
    ('e@example.com', NULL,     'free');

SELECT COUNT(*)                 AS all_rows,
       COUNT(referrer)          AS rows_with_referrer,
       COUNT(DISTINCT referrer) AS different_referrers,
       COUNT(DISTINCT plan)     AS different_plans
FROM signups;

Output

OUTPUT

+----------+--------------------+---------------------+-----------------+
| all_rows | rows_with_referrer | different_referrers | different_plans |
+----------+--------------------+---------------------+-----------------+
|        5 |                  3 |                   2 |               2 |
+----------+--------------------+---------------------+-----------------+
1 row in set

Form	Counts
`COUNT(*)`	Rows, whatever their values
`COUNT(column)`	Rows where the column is not NULL
`COUNT(DISTINCT column)`	Different non-NULL values

COUNT(1) and COUNT() are the same thing. Neither is faster than the other. COUNT() is the clearer one.

Counting with a condition

There is no FILTER clause in MySQL. Use CASE, or the fact that COUNT ignores NULL:

query.sql

SQL

SELECT COUNT(*) AS everyone,
       COUNT(CASE WHEN plan = 'pro' THEN 1 END) AS pro_count,
       SUM(plan = 'free') AS free_count,
       SUM(referrer IS NULL) AS no_referrer
FROM signups;

Output

OUTPUT

+----------+-----------+------------+-------------+
| everyone | pro_count | free_count | no_referrer |
+----------+-----------+------------+-------------+
|        5 |         2 |          3 |           2 |
+----------+-----------+------------+-------------+
1 row in set

SUM(condition) works because MySQL conditions return 1 or 0. COUNT(CASE WHEN ... THEN 1 END) is the portable form, since the CASE returns NULL when the condition is false.

Counting per group

query.sql

SQL

SELECT plan, COUNT(*) AS signups FROM signups GROUP BY plan ORDER BY plan;

Output

OUTPUT

+------+---------+
| plan | signups |
+------+---------+
| free |       3 |
| pro  |       2 |
+------+---------+
2 rows in set

Count rows that do not exist: zero counts

GROUP BY shows only groups that exist. A plan with zero signups is missing from the list. To show it, join from a table of plans with a LEFT JOIN (LEFT JOIN):

query.sql

SQL

CREATE TABLE plans (name VARCHAR(10) PRIMARY KEY);
INSERT INTO plans VALUES ('free'), ('pro'), ('enterprise');

SELECT p.name, COUNT(s.id) AS signups
FROM plans p
LEFT JOIN signups s ON s.plan = p.name
GROUP BY p.name
ORDER BY p.name;

Output

OUTPUT

+------------+---------+
| name       | signups |
+------------+---------+
| enterprise |       0 |
| free       |       3 |
| pro        |       2 |
+------------+---------+
3 rows in set

Count a column of the right-hand table (s.id), not COUNT(). COUNT() would count the empty row of the left join as 1.

COUNT performance

InnoDB has no stored row count. COUNT(*) must count the rows, usually by scanning the smallest index. On a table with 100 million rows, that takes seconds.
A WHERE on an indexed column makes the count fast, because it counts only the index range.
If a rough number is enough, read the estimate: SELECT table_rows FROM information_schema.tables WHERE ..., or keep your own counter in a summary table.
COUNT(DISTINCT x) is slower still, because it must remove duplicates.

query.sql

SQL

SELECT COUNT(*) AS big_count FROM (SELECT 1 FROM signups LIMIT 1000) AS capped;

Output

OUTPUT

+-----------+
| big_count |
+-----------+
|         5 |
+-----------+
1 row in set

Counting up to a limit (here 1,000) is a way to show “1000+” quickly on a huge table.

COUNT returns a BIGINT

The result is never NULL and is never an error: counting zero rows gives 0. That makes COUNT the safe answer to “are there any rows?”. For an existence check, EXISTS is faster, because it stops at the first row (EXISTS).

warning

Counting joined rows

After joining a parent to its children, COUNT(*) counts child rows, not parents. Use COUNT(DISTINCT parent.id) to count the parents (Aggregate Pitfalls).