Hi,

> if i add an OR condition to the where clause then the time ballons out
> again with 99206 un-indexed reads on the PRODUCT table. if i make it
> an AND condition then the query remains really fast. is there any way
> to speed OR up?

If with OR not every condition is able to use an index then the whole table needs to be scanned.

Please remove the LEFT JOIN at the begin this will force a join order and unneeded fetches.

SELECT
p.PRODUCTID,
pn.NAME,
t.NAME,
LEVENSHTEINC(UPPER(t.NAME), 'AFTER THE BALL')
FROM
TITLE t
JOIN PRODUCT p ON (p.TITLEID = t.TITLEID + 0)
JOIN PRODUCT_PARTICIPANT pp ON (pp.PRODUCTID = p.PRODUCTID)
JOIN PARTICIPANT_NAME pn ON (pp.PARTICIPANTID = pn.PARTICIPANTID)
WHERE
t.METAPHONE = METAPHONE('after the ball') OR
t.NAME CONTAINING 'after the ball'
ORDER BY
4, 3, 2

I assume that the filter on the TITLE table is very strong so i force the order by using "+ 0" (assuming TitleID is
numeric).
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)

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