Re: [Firebird-devel] Count(*) faster without index ?

[Nikolay S. <nik...@re...>]

Adam, Ann,

> But there is a conceptual problem with counts - most of the time people
> don't care. I might care whether there is 200 or 100,000,000, but the
> fact that there is exactly 98,433,123 is no more helpful for the end
> user than an estimation of 98,500,000. Certainly it might be helpful to
> provide a fast shortcut to an estimation, providing the syntax was such
> that it was obvious that the result is not exact.
>   
One way to do show Count(*) and other aggregates quickly and accurately 
is to materialize views with aggregates computed for the relevant groups 
of rows.
Materialized views can be updated on each transaction, if necessary and 
used "like index" to evaluate query quickly.
Replication techniques can be used to maintain materialized views 
efficiently.

Query can than be rewritten by the engine to execute against 
materialized view instead of original table.
We use this approach in production in our MDX engine for Firebird (based 
on Mondrian).

-- 
Nikolay Samofatov, MBA
Red Soft International
+1 416 710 6854