ansaurus

Question

SQL Query: Which one should i use? count("columnname"), count(1)

Answer 1

+1 A:

Will give different results if columnname can be NULL, otherwise identical performance.

The optimiser (SQL Server at least) realises COUNT(1) is trivial. You can also use COUNT(1/0)

gbn 2010-01-05 08:34:20

What does it means that count(1) is trivial?and how it differ from count(1/0)Please explain more.

Hemant Kothiyal 2010-01-05 08:53:04

1 is a non-null constant. So, it works like COUNT(*)

gbn 2010-01-05 09:06:22

Answer 2

+1 A:

It depends what you want to do.

The first one counts rows with non-null values of columnname. The second one counts ALL rows.

Which behaviour do you want? From the way your question is worded, I guess that you want the second one.

Mark Byers 2010-01-05 08:34:38

So it means i should use first one because it only count notnull values of column given. But in second one it count all rows of table as per condition given in where clause.Am i right?

Hemant Kothiyal 2010-01-05 08:51:45

Answer 3

+2 A:

There can be differences between count(*) and count(column). count(*) is often fastest for reasons discussed here. Basically, with count(column) the database has to check if column is null or not in each row. With count(column) it just returns the total number of rows in the table which is probably has on hand. The exact details may depend on the database and the version of the database.

Short answer: use count(*) or count(1). Hell, forget the count and select userid.

You should also make sure the where clause is performing well and that its using an index. Look into EXPLAIN.

Schwern 2010-01-05 08:45:53

Answer 4

+2 A:

I'd like to point out that this:

select count(*) from tablename where userid=:userid

has the same effect as your second solution, with th advantage that count(*) it unambigously means "count all rows".

The * in COUNT(*) will not expand into all columns - that is to say, the * in SELECT COUNT(*) is not the same as in SELECT *. So you need not worry about performance when writing COUNT(*)

The disadvantage of writing COUNT(1) is that it is less clear: what did you mean? A literal one (1) may look like a lower case L (this: l) in some fonts.

Roland Bouman 2010-01-05 08:49:33

Thanks,Does it make any difference by datatype of columnname; when we write count(columname) ; here columnname is string or integer

Hemant Kothiyal 2010-01-05 09:02:22

It could, but it really depends on how smart your optimizer is. I am pretty sure `COUNT(*)` is optimized in all RDBMS-es to simply count the number of rows, regardless of column values. You'd have to benchmark to be sure of course, but I expect `COUNT(*)` to be marginally faster in almost all RDMBS-products - that said, the margin will probably be neligibly small for the vast majority of cases. That said - I think the argument of writing clear code is more important in this case. It is very rare to find a db whose only performance problem is choosing betweeen `COUNT(1)` and `COUNT(*)`

Roland Bouman 2010-01-05 09:07:30

To make it worse, some database systems might allow you to name a column "1" or [1], thus the 1 could be a valid column name. Then again, any database developer who does this needs to find another job or just hit himself with a huge book about databases until he's comatose... Still, it could be done... SQL Server will allow this foolishness...

Workshop Alex 2010-05-06 12:45:10

Workshop Alex, sure, you can name the column 1, but surely, the 1 won't be parsed as a column name unless you apply identifier quotes. Or else, explain which RDBMS product you have in mind...

Roland Bouman 2010-05-06 14:42:17

Answer 5

+1 A:

To count the number of records you should use the second option, or rather:

select count(*) from tablename where userid=:userid

You could also use the exists() function:

select case when exists(select * from tablename where userid=:userid) then 1 else 0 end

It might be possible for the database to do the latter more efficiently in some cases, as it can stop looking as soon as a match is found instead of comparing all records.

Guffa 2010-01-05 08:50:35

Thanks,As it is mentioned * in inner query. Can't it be slow because you are first getting all coulmns from table and then checking existence.Please explain more?

Hemant Kothiyal 2010-01-05 09:04:11

@Hemant: The exists() function doesn't actually get the data, the database knows to only check if records exist. This is one of the few cases where you can safely use "select *".

Guffa 2010-01-05 09:41:53

Answer 6

A:

Hey how about Select count(userid) from tablename where userid=:userid ? That way the query looks more friendly.

Tesnep 2010-01-05 09:20:23

Friend,You need to revisit my question and comments given by others. I think you are out of context

Hemant Kothiyal 2010-01-05 10:14:41

That's funny. I looked at the answer marked as correct and the user suggests there to use select userId instead of select count(column). If you're are trying to get int as return value (which count() returns) how would you use that? You would want to retrieve int instead of the whole row to make the query execute faster. Why would you want to return the whole row if you want to check existence? Anyway for readability (I suppose other developers also read your query) I thought using count(userid) instead of other column name would help. So I am in the context right?

Tesnep 2010-01-05 11:06:15

ansaurus

tags:

views:

answers:

SQL Query: Which one should i use? count("columnname"), count(1)

related questions