At 11:23 PM 26/11/2003 +0100, Thomas S. wrote:

>Christian,
>
> > please take a look at the following table:
> >
> > CREATE TABLE T_KUNDEN (
> > ID INTEGER NOT NULL,
> > KID INTEGER NOT NULL,
> > NACHNAME VARCHAR(80) COLLATE DE_DE,
> > FIRMA VARCHAR(80) COLLATE DE_DE,
> > [...]
> > );
> >
> > ... and the following indices:
> >
> > CREATE INDEX T_KUNDEN_FIRMA ON T_KUNDEN (FIRMA);
> > CREATE INDEX T_KUNDEN_NACHNAME ON T_KUNDEN (NACHNAME);
> >
> > ... and the following select statement:
> >
> > SELECT * FROM T_KUNDEN
> > ORDER BY FIRMA ASC, NACHNAME ASC
> >
> > Why does Firebird ignore the indices and uses the following plan?
> >
> > Plan
> > PLAN SORT ((T_KUNDEN NATURAL))
> > Adapted Plan
> > PLAN SORT ((T_KUNDEN NATURAL))
>
>Indices are your best friend if you want to get a small
>result set out of a huge table, specified by a WHERE
>clause. Using indices when the engine has to visit all
>records in a table (for example if you want all records
>back, cause you haven't specified a WHERE clause) is
>usually a bad thing, because a FULL TABLE scan is
>"cheaper" without using an index.

Thomas,
This doesn't seem to be the whole answer. The indexes *should* be used for
the sort. I wonder whether Christian is testing this on a table with very
few rows in it...natural sort order would make sense in that case.

Or this could be a situation of duplicated indexes. Is firma linked to
another table by a foreign key, perhaps?

heLen