Hi Martin!

Looking through 200000 records in 8 seconds possibly even forced to
use indexes, isn't exactly slow - in my opinion.

First, Firebird gets your result set and orders it by asterm and id,
and only then it selects the first record. Put simply, Firebird must
know the entire result set to know which is to be the first record.

Unless you have a significant amount of records with id <= 0, then you
may be able to speed up your query a bit by simplifying and changing to

select first 1 id from dicentries
where asterm is not null and id+0 > 0
/*I think '' is less than any other value, so no need for OR*/
order by asterm, id

Though I think that to make it quick, you better split your request
into two separate requests:

select min(asterm) from dicentries
where asterm is not null and id+0 > 0
//May use an index on asterm and can be quick

select min(id) from dicentries
where asterm = :resultfromabove and id > 0
//May use indexes on both asterm and id and should be quick

There may exist some quicker and better solution, but SELECT FIRST
will be slow when the 'temporary result set' (before doing the FIRST)
is big and ordered.

Good luck,
Set

--- In firebird-support@yahoogroups.com, "martinknappe" wrote:
> hi,
> i have here the script of a database:
>
> http://www.yourfilehost.com/media.php?cat=other&file=script.sql
>
>
> now, i've created the database and inserted some 200 000 random
> entries in table 'dicentries'
> when i execute the following query:
>
> select first 1 id from dicentries where (asterm = '' and id > 0)
> or
> (asterm > '' and id > 0) order by asterm ascending, id ascending
>
> it takes around 8 seconds to execute!
> will i have to put up with this or does any of you see a way to
> speed this up?
>
> thanx very much,
>
> martin