--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> Returning only 500 records, it is probably better not to use the index for ANAME. You might change your query to:
>
> SELECT
> ID, ANAME
> FROM MYTABLE
> WHERE ANAME LIKE '%TEST%'
> AND DEL+0 = 0
> ORDER BY ANAME||''
>
> I don't know how much it will improve,

Great tip :) !!! The DB is on the client side, so i ask him to test Your sugestion:

Old version:
Prepare time / Total execution time
0.096s / 29.742s
0.172s / 23.428s
0.175s / 24.891s
0.190s / 30.911s
0.183s / 25.198s
0.181s / 37.458s

New version (with ||''):
Prepare time / Total execution time
0.038s / 2.686s
0.031s / 2.116s
0.031s / 1.800s
0.033s / 1.760s
0.032s / 1.837s


P.S. Instead of 150s now there is 30s in original version - I guess during previous tests server was under heavy load.

> I was surprised about you saying that it was 100 times
> quicker without the ORDER BY. Are you sure that you measured
> how much longer it took to return the entire result set,
> and not just the first few rows? I would expect

I'm not sure how to test that, that statistics from FR:

Starting transaction...
Preparing query: SELECT
ID, ANAME
FROM MYTABLE
WHERE ANAME LIKE '%TEST%'
AND (1=0 OR DEL=0)
ORDER BY ANAME
Prepare time: 0.181s
Field #01: MYTABLE.ID Alias:ID Type:INTEGER
Field #02: MYTABLE.ANAME Alias:ANAME Type:STRING(250)
PLAN (MYTABLE ORDER ANAME_IDX)

Executing...
Done.
510103 fetches, 375 marks, 68902 reads, 346 writes.
0 inserts, 0 updates, 0 deletes, 97174 index, 0 seq.
Delta memory: -45104 bytes.
Total execution time: 37.458s
Script execution finished.

Plan for the new version of query is:

PLAN SORT ((MYTABLE NATURAL))

<><><>

So, what all this means ? Using large index for sorting small resultset is ineffective ?

> HTH,
> Set

Thanks a lot for Your Help !
b