tags:

views:

766

answers:

8

Hello guys,

I often find these three variants:

SELECT COUNT(*) FROM Foo;
SELECT COUNT(1) FROM Foo;
SELECT COUNT(PrimaryKey) FROM Foo;

As far as I can see, they all do the same thing, and I find myself using the three in my codebase. However, I don't like to do the same thing different ways. To which one should I stick? Is any one of them better than the two others?

+5  A: 

This will depend on the type of database you are using as well as the type of table in some cases.

For example, using MySQL, count(*) will be fast under a MyISAM table but slow under an InnoDB. Under InnoDB you should use count(1) or count(pk).

Jarod Elliott
Interesting. Is there any benchmark on the matter you could link to?
zneak
Sorry, not aware of any specific benchmarks. Its just a common known "issue" with dealing with MySQL. A google search should point at numerous articles discussing the issue. Basically, MyISAM stores the total number of rows with the table information whilst InnoDB doesn't. I believe count(*) can be fast for InnoDB under certain situations but i find it safer to always use count(key) - no surprise performance issues that way.
Jarod Elliott
`count(key)` (unique keys and foreign keys (not primary key ones), unique/foreign can be nullable) can have performance surprises issues, look at Mitch Wheat answer. Just think of COUNT(\*) as a C language function COUNT(), i.e. the `*` is ignored. If there will be performance issue between COUNT(*) and COUNT(key) on non-optimizing RDBMS, i think it's the latter that could suffer from performance, the parameter key in COUNT(key) gets evaluated on each row. While the `*` is just a directive for RDBMS to just fetch the metadata for count, or the cardinality of table. Read Mitch Wheat's answer
Hao
+3  A: 

At least on Oracle they are all the same: http://www.oracledba.co.uk/tips/count_speed.htm

ZeissS
That's good news.
zneak
+4  A: 

COUNT(*) and COUNT(1) are actually subtley different (though they do the same thing)

COUNT(*) is the cardinality of the table (i.e. the number of rows) and COUNT(exp) is the count of the non-NULL occurrences of the expression, which happens to be a constant in this case.

I prefer COUNT(*)

Mitch Wheat
+17  A: 

Two of them always produce the same answer:

  • COUNT(*) counts the number of rows
  • COUNT(1) also counts the number of rows

Assuming the 'pk' is a primary key and that no nulls are allowed in the values, then

  • COUNT(pk) also counts the number of rows

However, if 'pk' is not constrained to be not null, then it produces a different answer:

  • COUNT(possibly_null) counts the number of rows with non-null values in the column possibly_null.

  • COUNT(DISTINCT pk) also counts the number of rows (because a primary key does not allow duplicates).

  • COUNT(DISTINCT possibly_null_or_dup) counts the number of distinct non-null values in the column possibly_null_or_dup.

  • COUNT(DISTINCT possibly_duplicated) counts the number of distinct (necessarily non-null) values in the column possibly_duplicated when that has the NOT NULL clause on it.

Normally, I write COUNT(*); it is the original recommended notation for SQL. Similarly, with the EXISTS clause, I normally write WHERE EXISTS(SELECT * FROM ...) because that was the original recommend notation. There should be no benefit to the alternatives; the optimizer should see through the more obscure notations.

Jonathan Leffler
I didn't even know `COUNT(DISTINCT)` worked, though it makes sense. Is it specific to a SQL flavor, or it's widely supported?
zneak
@zneak: COUNT(DISTINCT x) has been in SQL since SQL-86 (the first standard), so I would be surprised to find any SQL DBMS that did not support it.
Jonathan Leffler
+20  A: 

Use * for all your queries that need to count everything, even for joins, use *

SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

But don't use COUNT(*) for joins, as that will return 1 even if the subordinate table doesn't match anything from parent table

SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

Don't be fooled by those advising that when using * in COUNT, it fetches entire row from your table, saying that * is slow. The * on SELECT COUNT(*) and SELECT * has no bearing to each other, they are entirely different thing, they just share a common token, i.e. *. In fact, if it is not permitted to name a field as same as its table name, RDBMS language designer could give COUNT(tableNameHere) the same semantics as COUNT(*). Example:

For counting rows we could have this:

SELECT COUNT(emp) FROM emp

And they could make it simpler:

SELECT COUNT() FROM emp

And for LEFT JOINs, we could have this:

SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

But they cannot do that (COUNT(tableNameHere)) since SQL standard permits naming a field with the same name as its table name:

CREATE TABLE fruit -- ORM-friendly name
(
fruit_id int NOT NULL,
fruit varchar(50), /* same name as table name, 
                and let's say, someone forgot to put NOT NULL */
shape varchar(50) NOT NULL,
color varchar(50) NOT NULL
)

And also, it is not a good practice to make a field nullable, say you have values 'Banana', 'Apple', NULL, 'Pears' on fruit field. This will not count all fruits, it will only yield 3, not 4

SELECT count(fruit) FROM fruit

Though some RDBMS do that sort of principle(for counting the table's rows, it accepts table name as COUNT's parameter), this will work in Postgresql (if there is no subordinate field in any of the two tables below, i.e. as long as there is no name conflict between field name and table name):

SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

But that could cause confusion later if we will add a subordinate field in the table, as it will count the field(which could be nullable), not the table rows.

So to be on the safe side, use:

SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

[EDIT]

In particular to COUNT(1), it is a one-trick pony, it works well only on one table query:

SELECT COUNT(1) FROM tbl

But when you use joins, that trick won't work on multi-table queries without its semantics being confused, and in particular you cannot write:

-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

So what's the meaning of COUNT(1) here?

SELECT boss.boss_id, COUNT(1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

Is it this...?

-- counting all the subordinates only
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

Or this...?

-- or is that COUNT(1) will also count 1 for boss regardless if boss has a subordinate
SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

Though it isn't hard to infer (though some could be confused) that COUNT(1) is the same as COUNT(*) regardless of type of join. But for LEFT JOINs result, we cannot mold COUNT(1) to work as: COUNT(subordinate.boss_id), COUNT(subordinate.*)

So just use either of the following:

-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

Use either COUNT(field) or COUNT(*), and stick with it consistently. In short, don't use COUNT(1) for anything

Michael Buen
Thanks for the advice. Are there reasons behind those imperatives?
zneak
Imperatives regarding using `*` consistently? Yes there is, to make things simpler and consistent. In fact if SQL standard ruled that a field name could not have the same name as its table name, there could be only two forms of COUNT, one is `COUNT()` and the other is `COUNT(tableOrAliasedTablenameHere)`, life could be simpler. If we think about it, we don't count field, we count rows. And if SQL standard ruled that there could be no nullable fields in the database, we really won't need `COUNT(fieldnameHere)` construct at all.
Michael Buen
If it's only for the sake of consistency, then you could also use COUNT(1) instead of COUNT(*). Is one of them better than the other?
zneak
COUNT(1) looks like a magic number, one that is used when someone already have a grasp what is going on under-the-hood. It could led to abuse (i.e. if there's a malicious intention), since all of COUNT(0), COUNT(1), COUNT(2), COUNT(42) (you get the gist) are the same as COUNT(`*`), somebody could obfuscate the code and use COUNT(2) for example, so the next maintainer could have a hard time deducing what those COUNTs do. Someone will only start to use COUNT(1) when he/she already gleans that COUNT(1) is same as COUNT(`*`). Nobody started their database career with COUNT(1)
Michael Buen
If for consistency's sake, COUNT(*) is better, it doesn't has any arcane semantics from it as compared to COUNT(1). Everybody starts with `SELECT * FROM tbl`. And it naturally occurs to them(and rest of us) that when they need to get that query's count, they will just enclose COUNT on *. It won't be a first nature to us to use COUNT(1)
Michael Buen
+1 for pointing out that `COUNT(*)` and `*` are very different. Unless there's a WHERE clause filtering the result set, `COUNT(*)` will often be processed by the DBMS looking at the metadata it maintains for the number of rows in the table - never reading a row from the table. This does depend on the DBMS optimizer, but it is such a massive benefit that they all (or is it just 'almost all'?) do it.
Jonathan Leffler
yeah you are right about the count metadata. `SELECT COUNT(*) FROM tbl` will fetch metadata for the table's rows count, if the database isn't of MVCC type, notably MySQL's MyISAM, COUNT(*) is indeed very fast. On 'almost all', the database can't store row count metadata if it implements MVCC, e.g. MySQL InnoDB, Postgresql, Sql Server 2005(and up, though have to explicitly set the database as MVCC type)
Michael Buen
or from jester programmers, they could do: `SELECT COUNT('ME IN') FROM tbl`, for the thinking that like 1 in `COUNT(1)`, 'ME IN' will be ignored and optimized by the RDBMS also
Hao
A: 

I feel the performance charecteristics changes from DBMS to DBMS. Its all on how they choose to implment it. Since I have worked extensively on oracle, Ill tell from that perspective.

COUNT(*) - Fetches entire row into result set befor passing on to the count function, count function will aggregate 1 if the row is not null

COUNT(1) - Will not fetch any row, instead count is called with a constant value 1 for each row in the table when the where matches.

Count(PK) - PK's in oracle is indexed. This means Oracle has to read only the index. Normally one row in Index B+ Tree is many times smaller than the actual row. So considering the disk IOPS rate, Oracle can fetch many times more rows from Index with a single block transfer as compared to entire row. This leads to higher througput of the query.

From this you can see the first count is the slowest and the last count is the fastest in Oracle.

arunmur
A: 

As it is obvious Jonathan Leffler told the complete story. but in similarcases Irecommand to use SQL Profiler and check whats happening.

There you can find your answer.

Nasser Hadjloo
Which SQL flavor does SQL Profiler work with?
zneak
@zneak - Go to StartMenu>All Programs\Microsoft SQL Server 2xxx \Performance Tools\SQL Profiler
Nasser Hadjloo
@Nasser Hajloo: Unfortunately, I don't use SQL Server; and even if I did somehow, I guess I can't run SQL Profiler from a non-Windows machine. Thanks for the tip, though.
zneak
A: 

Asked and answered before...

Books on line says "COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )"

"1" is a non-null expression so it's the same as COUNT(*). The optimiser recognises it as trivial so gives the same plan. A PK is unique and non-null (in SQL Server at least) so COUNT(PK) = COUNT(*)

This is a similar myth to EXISTS (SELECT * ... or EXISTS (SELECT 1 ...

And see the ANSI 92 spec, section 6.5, General Rules, case 1

        a) If COUNT(*) is specified, then the result is the cardinality
          of T.

        b) Otherwise, let TX be the single-column table that is the
          result of applying the <value expression> to each row of T
          and eliminating null values. If one or more null values are
          eliminated, then a completion condition is raised: warning-
          null value eliminated in set function.
gbn