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

In my SQL query I just need to check whether data exists for a particular userid.

I always only want one row that will be returned when data exist.

I have two options

1. select count(columnname) from table where userid=:userid

2. select count(1) from tablename where userid开发者_运维百科=:userid

I am thinking second one is the one I should use because it may have a better response time as compared with first one.

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.

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.

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)

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.

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.

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