Subject RE: [Firebird-Architect] Inexact database operations
Author Rick Debay
This has been brought up before. If it were implemented,
APPROXIMATE_COUNT would probably always return 42.
There are other solutions to returning an approximate number in a web
Off the top of my head, the value could be in the response after the
results, so that the results are rendered in the browser^H^H^H^H^H^H
user-agent, and the total rendered soon after.
If it's stateful (due to sessions for example), the value could be
updated after the response is sent.

-----Original Message-----
[] On Behalf Of Alexander
Sent: Tuesday, June 28, 2005 4:27 AM
Subject: [Firebird-Architect] Inexact database operations

As promised in a message to "Bi-directional indexes" thread, I want to
raise the following issue:

It was just recently said in this group that search engines are a
terrible model for the databases, but the problem is -- the use cases
for both types of systems are often identical.
Users DO want to issue non-exact queries that matches perhaps a million
records in a database, browse a few (or not so few) pages of the
resultset, then re-formulate a query to narrow search, etc.
The solution I use to the first problem that occurs here -- effective
browsing of large resultset -- is persented in my previous mail, and I
am quite happy with it.
The second problem is much more difficult -- I want to estimate the
number of records in the resultset *without* fetching them all.

I understand well that "select count(*)" query is very cheap in some
databases (like MySQL) and very expensive in others (like Firebird).
But here is my heretic proposal: implement "APPROXIMATE_COUNT(*)" SQL
function, that would return the number of, say, index entries without
trying to materialize every record involved.
Note that the usage outlined above is not too concerned with the exact
accuracy of the counters -- it just needs an estimate. And, with
web-interface there can not be any consistency guarantee anyway --
because the transaction can not span more than one HTTP request.
In similiar vein, "APPROXIMATE_MIN" and "APPROXIMATE_MAX" functions
might also be of some use.

Alexander S. Klenin
Insight Experts Ltd.