Hello, I have a question on a performance analysis I just did and I don't understand.

I have a query on a table with many records and the performance depends on something, I cannot understand.

The table has an integer field and a timestamp field. Now I want to select the first record, with a specific value in the integer field ordered by the timestamp field.

There are indices on both fields and a combined index of both fields.

When I do it that way:

select first 1 timestampfield

from mytable

where integerfield = 1

order by timestampfield

it is much slower (up to 7 times slower, depending on how many records I filled into the table) than this one

select first 1 timestampfield

from mytable

where integerfield = 1

order by integerfield, timestampfield

even though the result is the same and the performance analyser shows 1 indexed read on both.

Can anyone help me and explain that?

Normally it should be the same. I do not see any sense in doing an ordering on a field, that is the same in every single record in the result, because it is filtered on this field.

I created an example where it is reproducable.

Here is the script for creating the table and filling it with records.

set term ^;

create table test

   (test_id integer primary key not null,

    field1  integer,

    field2 timestamp)^

create index test_field1 on test (field1)^

create index test_field2 on test (field2)^

create index test_field12 on test (field1, field2)^

create or alter procedure fill_test as

declare variable i integer;

declare variable n integer;

declare variable x integer;

begin

n = 100000;

x = 0;

while (:x < 10) do

   begin

   i = 1;

   while (:i < :n) do

      begin

      insert into test (test_id, field1, field2)

      values (:x * :n + :i, :x, (select current_timestamp from rdb$database));

      i = :i + 1;

      end

   x = :x + 1;

   end

end^

execute procedure fill_test^

commit^

And here is the quick query:

select first 1 field2 from test

where field1 = 5

order by field1, field2

And here is the slow query:

select first 1 field2 from test

where field1 = 5

order by field2