Re: Improving count(*)

On Fri, Nov 18, 2005 at 03:46:42PM +0000, Richard Huxton wrote:
> Simon Riggs wrote:
> >One of the major complaints is always "Select count(*) is slow".
> Although there seem to have been plenty of ideas on this they all seem
> to just provide a solution for the "whole table" case. It might be that
> the solution provides other benefits, but for this one case it does seem
> like a lot of work.

Or, it wasn't explained properly as to how the WHERE clause would
function.

The solution involving an index that has visibility information should
work fine with a WHERE clause. Only index rows that match the clause
are counted.

A solution enhancing the above mentioned indexes, to maintain a count
for whole index blocks, would allow whole index blocks that satisfy
the WHERE clause to be counted, assuming the whole index block is
visible in the current transaction.

Not to say these are the best ideas, or the only ideas - but it isn't
true that most of the solution presented only apply to the 'whole table'
case. The *simplest* solutions, apply only to the 'whole table' case. :-)

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/