Re: Optimizing a Simple 'select count(*)'

From: Mark Bobak <>
Date: Fri, 7 Mar 2014 19:58:29 +0000
Message-ID: <>

You're right. I don't know what I was thinking. The NOT NULL constraint business would only make any sense if you were doing a count(*) on all rows in the table. Then the optimizer would look for an index on a column that has a NOT NULL constraint (or a 'column_name is not null' predicate in the query) before it would use the index.

I need to read more closely before I start typing....sorry.


From: <Tefft>, Michael J <<>> Date: Friday, March 7, 2014 at 2:23 PM
To: Mark Bobak <<>>, "<>" <<>>, "<>" <<>> Subject: RE: Optimizing a Simple 'select count(*)'

It is not necessary for completion_time to be NOT NULL for a index on that column to be suitable for this query.

Suppose you have rows that have completion_time as null? So what? That predicate will exclude them anyway - and the optimizer knows this, and it will gladly use an index on completion_time if it exists (and if it makes sense). The biggest question is: Does an index actually exist, with that column?

I don't think that has not been explicitly stated, and I have had (a few) occasions when I struggled for hours with the question 'why won't it use the index on that column' because I assumed it had to exist, but never verified it was really there.


-- Received on Fri Mar 07 2014 - 20:58:29 CET

Original text of this message