4

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.

Edward
  • 3,292
  • 1
  • 27
  • 38
Hemant Kothiyal
  • 4,092
  • 19
  • 61
  • 80
  • see also this *almost* duplicate: http://stackoverflow.com/questions/1221559/count-vs-count1 – Kobi Jan 05 '10 at 08:37
  • 2
    @Hermant: You don't have to apologize for asking questions. That's exactly what this place is for. – Mark Byers Jan 05 '10 at 08:39

6 Answers6

2

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
  • 153,029
  • 25
  • 195
  • 336
2

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
  • 31,125
  • 6
  • 66
  • 67
  • Thanks, Does it make any difference by datatype of columnname; when we write count(columname) ; here columnname is string or integer – Hemant Kothiyal Jan 05 '10 at 09:02
  • 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 Jan 05 '10 at 09:07
  • 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... – Wim ten Brink May 06 '10 at 12:45
  • 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 May 06 '10 at 14:42
1

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
  • 422,506
  • 82
  • 585
  • 676
1

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
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 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 Jan 05 '10 at 08:51
1

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
  • 687,336
  • 108
  • 737
  • 1,005
  • 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 Jan 05 '10 at 09:04
  • @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 Jan 05 '10 at 09:41
-1

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

Tesnep
  • 105
  • 1
  • 2
  • 11
  • Friend, You need to revisit my question and comments given by others. I think you are out of context – Hemant Kothiyal Jan 05 '10 at 10:14
  • 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 Jan 05 '10 at 11:06