Re: What to Count - WAS Re: Newbie ? - Howto find nbr records in a table

Alan,

>I like this idea. If my primary key is a compound key (i.e. more than one
>column) should I just choose any one or aggregate (concatenate) them to a
>single value.

If you aggregate the values to a single value, your index will not be used anymore ;-)

Suppose the index is built up with the fields A, B and C with the corresponding values '0000', 'XXXX' and '9999'. If you've created the index on the three fields, the index will look like this: '0000XXXX9999'. Considering this, I think it doesn't matter if you query with one field or all three.

If you have another non unique index on a field that doesn't contain NULL's (because they're not indexed!), you can also use this index. You will benefit from this index if it's keys are smaller than the other index and certainly if the indextree is 'flatter'. In the latter case, the indextree doesn't have much leaves which results in less reading.

>I'm not sure this idea will always work though as I find that when counting
>it's usually grouping by some combination which you can;t all get fromt he
>same index so you're back to hitting the table again.

If you're grouping on a non-indexed field, you will better have a full table scan. The index will only create overhead.

>From the rest of your answer though, it sounds like COUNT(*) is quicker than
>COUNT(9). In the former, no work at all is done, in the latter the
>expression '9' must be evaulated. Have I got this right?

I suppose so. I don't have an answer to that. An Oracle performance tuning book states: "We tested the following statements on several different computers and found that count(*) consistently runs between 15% and 20% faster than count(1), and that count(index_column) is 5% faster again. The following runs counted 65,536 rows from a table:

select count(unique index column) from transactions : 2.43 seconds 
select count(*) from transaction : 2.59 seconds
select count(1) from transaction : 3.47 seconds".

Will this do?

• Martin Jesterhoudt
• martinj(replace this by @)xs4all.nl

  ---