Hi Helen



The where clause looks for foreign_key, so NUMER must be take out of the
index!



Or ist he index used to find max(t.numer) in the result set?



Chris



Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von Helen Borrie
Gesendet: Mittwoch, 18. August 2010 12:16
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] query optimization- MAX() from .. WHERE





At 09:07 PM 18/08/2010, you wrote:

>Hi
>I've got table similar to the following example:
>
>CREATE TABLE TEST_TAB (
> ID INTEGER NOT NULL PRIMARY KEY,
> NUMER INTEGER,
> FOREIGN_KEY INTEGER);
>CREATE DESCENDING INDEX TEST_TAB_IDX1 ON TEST_TAB (FOREIGN_KEY, NUMER);
>
>and query like this:
>
>select max(t.numer)
> from test_tab t
> where t.foreign_key = :some_value
>
>PLAN (T INDEX (TEST_TAB_IDX1))
>
>So it uses proper index but Performance Analysis shows lot of reads (as

much as count() where foreign_key = :some_value).

>
>Is it any way to improve this query performance

Take FOREIGN_KEY out of that index!

./heLen





[Non-text portions of this message have been removed]