This is simply too big a difference between the first and subsequent
executions of the query. I suspect the cause has nothing to do with
the query at all, but rather something external like system restore on
some Windows version making backups of every .gdb file or your virus
scanner. Try running SELECT field_A from table_X where <primary key> =
<one existing value> after restarting your computer. If that is time
consuming, then the query you execute isn't the guilty party.

I suppose it could also be garbage collecting (which could happen when
you issue select count), have you tried running some statistics on
your database to see if there is a big gap between the oldest (active)
and the next transaction? Do you have indexes for fields containing
lots of duplicates? If so, add the primary key to the end of that
index - if possible. The symptom of something like this being the
cause, is simply that your initial select count-query is time
consuming if you restart the computer on a database where there has
been done lots of changes, whereas it is almost instant after a
restart when the database hasn't been changed at all since last time
you did a select count.

HTH,
Set

--- In firebird-support@yahoogroups.com, "donoteatcarrot" wrote:
> Thank you for your kind help.
>
> > Something is seriously wrong when
> > SELECT MAX(field_A) as max_A, field_B FROM TABLEX GROUP BY field_B
> > takes 20 minutes on a table that contains only 500000 records.
>
> and sorry for my poor explaination.
> Only after restarting the computer and I execute the query the first
> time, it takes about 20 minutes.
> there are also some UDFs, constraints, stored procedures, triggers
> in the database.
> but in this query:
> select max(field_A), field_B from table_X group by field_B
> table_X uses none of the UDFs, triggers ...
> Field_A's data type is timestamp,
> Field_B's data type is char(20).
> Default character set is UNICODE_FSS.
> Database file size is about 4G.
> Page size is 4K.
> and the second time of the execution takes about 15 seconds.
>
> why the first time takes so much more time ?
>
> And I found a document
> http://www.firebirdsql.org/downloads/makeibscream.pdf
> it sais ,
> "Columns used in aggregate functions, like COUNT() do not use an
> index."
> Is that means even I add index to the table, the execution can not
> be faster ? (because there is MAX() in that query)
> In fact, I added both ASC and DESC index. The time of execution
> really did not change so much.