As a workaround I suggest you retrieve all the results and count them, instead of asking the database to do it.
It looks like every record pointed to by the index should be visible to you, therefore it should be very quick.
Do both queries produce the same plan?

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Fabio Gomes
Sent: Tuesday, August 08, 2006 1:48 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Is there a way to optimize a count()? or am i doing it the wrong way?

I run 2 queries, one that just counts, and another that returns just 20 results, the only purpose of the count is to generate the paging numbers the query is very very fast.. some milliseconds.. but the count takes a lot of time.

Database header page information:
Flags 0
Checksum 12345
Generation 122091
Page size 4096
ODS version 10.1
Oldest transaction 122088
Oldest active 122089
Oldest snapshot 122089
Next transaction 122090
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 19
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Dec 28, 2005 7:40:25

On 8/8/06, Rick Debay <rdebay@...> wrote:
>
> If the query listed here will only return 20 results, the count
> should be fast.
> If it is not, then it's probably because you have lots of old versions
> of these records available, that COULD match I.pro_cod='1' if they
> were visible to your transaction.
>
> Can you show the plan, the current/oldest transaction numbers, and
> perhaps run a sweep and test again?
>
> Rick DeBay
>
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>[mailto:
> firebird-support@yahoogroups.com <firebird-support%40yahoogroups.com>]
> On Behalf Of Fabio Gomes
> Sent: Tuesday, August 08, 2006 12:49 PM
> To: firebird-support@yahoogroups.com
> <firebird-support%40yahoogroups.com>
> Subject: [firebird-support] Is there a way to optimize a count()? or
> am i doing it the wrong way?
>
> I have this query:
>
> SELECT count(I.vnd_cod)
> FROM items_venda I
> JOIN vendas N ON (N.vnd_cod=I.vnd_cod) JOIN clientes C ON
> (N.cli_cod=C.cli_cod) JOIN vendedores V ON (
> N.ved_cod=V.ved_cod) WHERE I.pro_cod='1'
>
> I m using it on php, so this code is just for the pagination, the main
> query will just select 20 rows, no more than it. but i need a count of
> all the rows so i can know how many rows the query returned, and pass
> it to my pagination function.
>
> We have a slow server here (P4 2.0ghz 512MB) this query takes about
> 20+ seconds to return and uses almost 100% of the cpu to do it.
>
> So, is there a way to optmize this count() function?
>
> I m asking it cause currently we have just one company using this
> application, but my boss said that almost sold it to another company
> that have a lot more data then the current one, so i m beggining to
> worry about the performance of some stuff, cause i use this in almost
> all reports, this one is the worst in performance, but wating 20
> seconds to show up 20 rows is too much!
>
> thanx for any help,
>
> Fábio.