Hi Adam, hi Set,
thanx for your replies. Creating a joint index on asterm, id sounds
very much like a plan. I just tried to do that with:

CREATE INDEX DICENTRIES_IDX1
ON DICENTRIES (ASTERM,ID)

And this is what I get:

key size exceeeds implementation restriction for index "DICENTRIES_IDX1"

I thought this may have to do with the fact that I am using
unicode_fss code point ordering on a field that may contain up to 80
characters and that the combined index of asterm, id may be too big..

given the fact that creating an index on asterm alone works fine, i
thought the extra 4 bytes from field id seem to be what makes this
combined index too big; so what i did was simply reduce the size of
field asterm from 80 characters to 70 characters (thereby reducing the
potential index size by 30) and try again - > and i'm still getting
this error!

how come it's fine to create an index on an 80-bytes varchar field
alone, but not, to create a combined one on a 70-bytes varchar field
and a 4-byte integer? what am i doing wrong here?

thanx very much,

martin

ps: i'm using firebird 1.5

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> > ive seen this same kind of application done with the bde where you
> > were also able to switch indices as you please and it was way
> > faster..bde is pretty old so i cant really believe that firebird
> > shouldn't be able to do it equally fast
> > admittedly, the bde database didnt contain several different tables
> > referring to each other like this one does, but as you can see, this
> > very query requires no searching in any other table so there should be
> > a quicker way to find the first entry that meets the condition
> > i dont have *too* much of an idea of sql i must admit so i
> > suppose/hope there's still some better way to formulate my query
>
> Martin,
>
> Your original query seems to me to me an overcomplicated way of
> running this query:
>
> select
> first 1
> id
> from dicentries
> where (asterm >= '' and id > 0)
> order by asterm ascending, id ascending
>
> I took a quick glance at your script, but could not find a joint index
> on asterm, id. Perhaps the index on asterm alone is not selective
> enough, and using an index to determine whether ID>0 when it is
> probably coming from a generator (ie all records have to meet that
> criteria anyway) seems to me to be redundant.
>
> So if that assumption is true, the query is even simpler.
>
> select
> first 1
> id
> from dicentries
> where (asterm >= '')
> order by asterm ascending, id ascending
>
> If you have an index on dicentries (asterm, id), it should perform
better.
>
> You should also be aware that Firebird is very much not like BDE
> (thankfully), and does not automatically ship the entire result to the
> client. Most components only pull back what is visible on the screen
> at a time, so you may be reinventing a wheel here. But it should also
> be noted that there are some things that BDE will always be faster at,
> well at least until you try and get more than one person to use it
> simultaneously. It is acceptable in some cases and as designed for a
> desktop database interface, but certainly doesn't scale well to client
> server.
>
> Adam
>