Hi,

>> 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.

I meant the result from your condition (t.METAPHONE = METAPHONE('after the ball') OR
t.NAME CONTAINING 'after the ball') on the table TITLE is only small set of the whole table.

> 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)))

The PLAN looks very good to me.
There can't be used an index on TITLE because your CONTAINING comparison can't use an index.
20 seconds looks very long to me, but how many products do you've in the final result?

> 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.

CONTAINING can't use an index thus adding more indexes has no effect.
For filtering 1 ASC-index on the NAME field is enough. Also note that FK en PK automaticly create indexes. So if you've
an FK on PRODUCT.TITLEID you don't need the index IDX_PRODUCT_TITLEID.
Doesn't the METAPHONE comparison already return all matches where 'after the ball' is part of the name? Are you sure you
need OR and not AND?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info