Structure of Example table:

Id, Integer (PK)
Name, Varchar(100)
Description, Text

I need to know if exists difference in performance between:

SELECT COUNT(*) FROM Example;

and

SELECT COUNT(Id) FROM Example;

Or does not exists differences?

asked 17 Nov '09, 11:37

Cesar's gravatar image

Cesar
97315
accept rate: 0%

edited 15 Mar '13, 18:12

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264


My vote: no diff!
In old database servers, count(Id) or count(1) was faster than count(*).

permanent link

answered 17 Nov '09, 12:00

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

edited 17 Nov '09, 12:05

In version 10.0 and above, COUNT( x ) is translated to COUNT(*) if x is an expression that is known to not allow NULL. In your example, I think you have Id as a primary key (non-NULL), and therefore COUNT(Id) is treated identically to COUNT(*) unless your Example table appears on the NULL-supplying side of an outer join.

For expressions Y that might be NULL, there are at least three ways that COUNT(Y) may be slower than COUNT(*):

  1. The expression Y needs to be computed. This means it must be fetched from the table if it is a column; if it is a more complex expression, it needs to be evaluated to determine if it is NULL. If the COUNT(Y) appears above a work table, the server may need to materialize Y in the work table so that it would be available at the group-by node.
  2. The server can not combine COUNT(Y1) and COUNT(Y2) for different expressions, and materialized views can not be used unless they have an appropriate COUNT(Y) that matches the query.
  3. The server can not perform semantic transformations that eliminate a table needed to compute Y. This also means that index-only retrieval is not possible if Y can not be recovered from the index.

I do not know of any reason why COUNT(Y) might be faster than COUNT(*). So, use COUNT(*) if you want to count rows, and COUNT(Y) if you really need to distinguish how many rows had a NULL value for Y.

permanent link

answered 10 Mar '10, 21:18

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

If the exact value is not important, but you just want to get an idea if 1000 or 1Mio entries exists you can use the super fast access to the column count of systab. This value is updated during each successful checkpoint.

select count from systab where table_name = 'Example'

Use case is for instance: The application wants to display total count of customers in the database in its status bar, updated every 5min...

permanent link

answered 11 Mar '10, 12:27

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

edited 11 Mar '10, 13:53

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826

@Breck: I wanted to correct "tabel_name", too, but seems unable to. Why don't I have an "edit" button for other people's post - am I still missing reputation in these times of boot-strapping?

(11 Mar '10, 13:57) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×239
×137
×95
×5

question asked: 17 Nov '09, 11:37

question was seen: 2,006 times

last updated: 15 Mar '13, 18:12