Hi Filip!

I would expect slow performance to be due to not having any where
clause limiting which records to return, not due to not using any
index. Are you sure using the index speeds up your query? If so, then
Arno is the person who can answer why no index is being used.

Anyway, since B.ID is the primary key, you should be able to work
around this using

SELECT
A.*,
(SELECT B.DESCRIPTION FROM TEST B WHERE B.ID = A.FID AS DESCRIPTION)
FROM
TEST_DETAIL A
ORDER BY
A.CODE,A.VNR

HTH,
Set

--- In firebird-support@yahoogroups.com, "flipmooooo" wrote:
>
> Hi,
>
> I'm having some trouble with the query below. Both tables contains
> alot off records. I don't understand why when using a left join the
> UC_TEST_DETAIL_CODE_VNR can't be used for ordering, which results in
> slow performance.
>
> SELECT
> A.*,
> B.DESCRIPTION
> FROM
> TEST_DETAIL A
> LEFT JOIN
> TEST B
> ON
> B.ID = A.FID
> ORDER BY
> A.CODE,A.VNR
>
> plan on executtion using a left join
> PLAN SORT (JOIN (A NATURAL,B INDEX (PK_TEST_ID)))
>
> plan on executtion using an inner join
> PLAN JOIN (A ORDER UC_TEST_DETAIL_CODE_VNR,B INDEX (PK_TEST_ID))
>
> When i add the inner join plan to the query when using a left join, i
> get :
> index cannot be used in the specified plan.
> index UC_TEST_DETAIL_CODE_VNR cannot be used in the specified plan.
>
> Greetings,
> Filip Moons
>
> DDL:
> CREATE TABLE TEST (
> ID BIGINT NOT NULL,
> CODE VARCHAR(20) NOT NULL,
> DESCRIPTION VARCHAR(40));
>
> ALTER TABLE TEST ADD CONSTRAINT UC_TEST_CODE UNIQUE (CODE);
> ALTER TABLE TEST ADD CONSTRAINT PK_TEST_ID PRIMARY KEY (ID);
>
> CREATE TABLE TEST_DETAIL (
> ID BIGINT NOT NULL,
> FID BIGINT,
> CODE VARCHAR(20) NOT NULL,
> VNR INTEGER NOT NULL,
> DESCRIPTION VARCHAR(40));
>
> ALTER TABLE TEST_DETAIL ADD CONSTRAINT UC_TEST_DETAIL_CODE_VNR UNIQUE
> (CODE, VNR);
> ALTER TABLE TEST_DETAIL ADD CONSTRAINT PK_TEST_DETAIL_ID PRIMARY KEY
> (ID);
> CREATE INDEX IDX_TEST_DETAIL_FID ON TEST_DETAIL (FID);