--- In firebird-support@yahoogroups.com, "prairiedechien"
<Christophe.Brunin@...> wrote:

>
> Hello,
>
> I have a table with 5.900.000 records and a unique index on one
> column. Firebird 1.53.
> I execute the following statement 'select count (*) from table'.

Your unique index can not help with the above query. The count is
subject to the transaction isolation it is run within, so counting the
number of nodes in the unique index would return the wrong value.

Something like 'select count(*) from table where ID between 1 and
1000' could use an index on ID to narrow down the work the count(*)
has to do, but every record that is counted must be checked to see
whether your transaction is allowed to know about it under MGA.

>
> The execution plan informs that the index is not used (Ok, not really
> an issue in this case). But the response time is always different and
> deos not seem to follow any rule (I expected it will decrease and
> take advantage of cached data).
>
> Some example:
> 1st execution takes 2.01 sec.
> 2nd : 7.43 sec
> 3rd : 8.54 sec
> 4th : 3.04 sec
> 5th : 2.03 sec
> 6th : 2.02 sec

As mentioned above, every record in table must be checked to see if
you are even allowed to see it. In the process, it does some
housework, removing obsolete record versions. Depending on the amount
of 'garbage' in the database, this can be a significant cost.

If you are checking if count(*) > 1, then that is pretty crazy. You
would be asking Firebird to count every record and tell you if that
number is bigger than one, when you don't want to count every record,
there is a more appropriate 'EXISTS' clause.

Adam