--- In firebird-support@yahoogroups.com, "donoteatcarrot"
<donoteatcarrot@y...> 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 ?

The second time you run a query will nearly always be faster because
Firebird will cache a lot of stuff (technical term). I wouldn't think
20 minutes is normal for this much data though.

Does the following index help?

CREATE INDEX MYINDEX on table_X (field_B, field_A desc);

>
> 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."

Maybe read out of context but not entirely accurate. If your aggregate
function has a where clause, then the index will be used to narrow
down the possible records, but the count itself must be done by
visiting each of the possible records and testing whether it is
visible to your transaction. Non MGA databases do not contain records
that have been deleted in the index, so counting the records is as
cheap as counting index nodes. MGA databases include this, because not
all transactions can see that the record is deleted. Therefore
counting the index nodes is not going to work.

> 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.
>

An ascending index wont be used for Max, a descending one will be if
available. If your fields are primary or foreign keys, then they
already have an ascending index, and adding another ascending index
can confuse the optimiser, but give the index suggested above a try.

> > -I like carrots...
> Thank you for you like carrots.
> I like too, just because "eatcarrot@y..." has been taken. :-)

I don't.

Adam