Hi Arno,

> I assume that the filter on the TITLE table is very strong so i

force the order by using "+ 0" (assuming TitleID is

> numeric).

i'm not quite sure what you mean by the filter being very strong.
TITLEID is INTEGER NOT NULL and the primary key.

> The optimizer can't see find out how good the TITLE filter is in

reality and that's why he put's the product table in

> the front.
>
> If the above query is still slow please post the returned PLAN and

statistcs (also nr. of records in the tables)

that query takes around 20 seconds with the OR CONTAINING condition
with 99206 un-indexed reads on the TITLE table (which is also the
number of records in the table). the PRODUCT and PRODUCT_PARTICIPANT
tables have 112933 records, and the PARTICIPANT_NAME table has 32948
records. the plan is...

Plan:
PLAN SORT (JOIN (T NATURAL, P INDEX (IDX_PRODUCT_TITLEID), PP INDEX
(PK_PRODUCT_PARTICIPANT), PN INDEX (PK_PARTICIPANT_NAME)))
Adapted plan:
PLAN SORT (JOIN (T NATURAL, P INDEX (IDX_PRODUCT_TITLEID), PP INDEX
(PK_PRODUCT_PARTICIPANT), PN INDEX (PK_PARTICIPANT_NAME)))

i originally only had a unique index on TITLE.NAME but adding an
ascending an descending index on NAME didn't make any difference to
the plan or the speed of the query.

RDB$STATISTICS for UQ_TITLE_NAME is 1.00800352811348E-5.

thanks,
mark