> I've added the order by clause and retried it with IBOConsole to check
> the statistics.
>
> The query is SELECT FIRST 10 * FROM MY_TABLE WHERE COLUMN1 > 100 ORDER
> BY COLUMN1, COLUMN2
>
> The table has the index MY_INDEX on (COLUMN1, COLUMN2).
> Total number of records in the table = 100,000.
> Size of one record is approx. 3000 bytes.
> Number of rows with (Column1 > 100) is 89901.
>
> The statistics returned is as follows:
> Execution Time: 28.0766
> Prepare Time: 0.0094
> Reads: 90420
> Write: 5
> Plan: PLAN (MY_TABLE ORDER MY_INDEX INDEX (MY_INDEX))
> Rows affected: 89901
>

Hi PK,

I was interested in your situation, so I used a table with about
140,000 records and did a similar query. There were no indices or
constraints on my table other than the compound index I defined to
keep it similar to your scenario.

Firebird 1.5 (iSQL set stats)

Current memory = 670228
Delta memory = 134900
Max memory = 943012
Elapsed time= 0.03 sec
Buffers = 75
Reads = 45
Writes 0
Fetches = 391

My Plan was PLAN (TMP ORDER IXTMP)

I find it puzzling that your query did so many reads. I wonder if you
don't have a lot of garbage in your table, and the slowness is
actually the records being garbage collected as it goes? Maybe try a
backup-restore cycle to be sure.

Also, I am not entirely convinced the plan you posted is valid. At
least I could not get it to work with my table and index names as it
complains about the word 'index'.

By the way, the reason Ivan said you need an order by has nothing to
do with this performance problem. It is simply that 'get me 10
arbitrary records that meet this criteria' is not usually a useful
piece of information. When you talk about the first 10 records, you
normally are thinking of an ordered set of records.

Adam