Re: Why count(*) doest use index?

On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard <glenn(at)zewt(dot)org> wrote:
> On Sun, Mar 6, 2011 at 5:41 AM, Martijn van Oosterhout <kleptog(at)svana(dot)org>
> wrote:
>>
>> If it's really really important there are ways you can use trigger
>> tables and summary views to achieve the results you want. Except it's
>> expensive and when people are told that all of the sudden the count(*)
>> performance isn't so important any more. :)
>
> That's often perfectly fine, with read-heavy, single-writer workloads.
>
> I definitely wish there was a way to create indexes to track counters on
> various types of queries, even if it eliminates write concurrency on
> affected writes.  Doing it by hand is a pain.

beyond what the stats system does you mean?

If you aren't interested in high concurrency count it really isn't all
that difficult -- just push table modifying queries into a procedure
and grab rows affected. Row level trigger can also do it but
performance will suck unless you are already doing all row by row
processing (in which case your performance already sucks).

The way to do this in with high concurrency is like the above, but
insert (not update) rows affected into a table modification log that
is rolled up on time interval or user demand so you don't serialize
access w/every statement. Or you dispense with all the fuss and grab
fee'n'easy approximate count from the stats system which is really
what people want 99% of the time.

In the old days this was much more complicated problem because to eek
every bit of oltp performance out of the server you had to disable the
stats collector. Today you don't, so let it do your work for you.

merlin