Only a single index on the partnumber column.
The plan looks like this:
Plan
PLAN (table ORDER IX_table_partnumber)

Adapted Plan
PLAN (table ORDER IX_table_partnumber)


To be precise I'm trying to emulate single record displays and the
movement of a sequentiel pointer through a sorted column.
My exact sql for a "next" is:
"select first(1) REF from lagerkarte Where ((BESTELLNR>:P1) or
(BESTELLNR=:P1 and REF>:P2)) Order by BESTELLNR,REF"

And the queryplan here looks like:
Plan
PLAN SORT ((LAGERKARTE INDEX (IX_LAGERKARTE_BESTELLNR,
IX_LAGERKARTE_BESTELLNR)))

Adapted Plan
PLAN SORT ((LAGERKARTE INDEX (IX_LAGERKARTE_BESTELLNR,
IX_LAGERKARTE_BESTELLNR)))

in the above example there is an ascending index on
1.) REF (which is primary key)
2.) BESTELLNR (=partno)

Even the "prior" sql like this:
"select first(1) REF from lagerkarte Where ((BESTELLNR<:P1) or
(BESTELLNR=:P1 and REF<:P2)) Order by BESTELLNR DESC,REF DESC"

Is using the same plan. So an ascending index might help on descending
orders as well?

The Use of REF in the where and order parts here is only necessary on
columns, which allow duplicates. So i might get this optimized by
simplifying the query on columns without duplicates.

Regards
Gunther

--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> G. Nau wrote:
> >
> > I use the following selects to search for a single record equal or
> > greater than the searchstring:
> > "select first(1) * from table where partnumber >= :search order by
> > partnumber"
> >
> > It's working well, but kind of slow on bigger tables (>20.000

records).

>
> What indexes do you have and what does the query plan look like?
>
> Regards,
>
>
> Ann