markd_mms wrote:

> 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?
>
> i tried different conditions like =, LIKE, CONTAINING but it didn't
> seem to make a difference.
>
> query...
>
> SELECT PRODUCT.PRODUCTID, PARTICIPANT_NAME.NAME, TITLE.NAME,
> LEVENSHTEINC(UPPER(TITLE.NAME), 'AFTER THE BALL')
> FROM TITLE
> LEFT JOIN PRODUCT ON PRODUCT.TITLEID = TITLE.TITLEID
> INNER JOIN PRODUCT_PARTICIPANT ON PRODUCT.PRODUCTID =
> PRODUCT_PARTICIPANT.PRODUCTID
> INNER JOIN PARTICIPANT_NAME ON PRODUCT_PARTICIPANT.PARTICIPANTID =
> PARTICIPANT_NAME.PARTICIPANTID
> WHERE TITLE.METAPHONE = METAPHONE('after the ball')
> OR TITLE.NAME CONTAINING 'after the ball'
> ORDER BY 4, 3, 2
>
> and plan...
>
> Plan:
> PLAN SORT (JOIN (JOIN (JOIN (TITLE INDEX (IDX_TITLE_METAPHONE_ASC),
> PRODUCT INDEX (IDX_PRODUCT_TITLEID)), PRODUCT_PARTICIPANT INDEX
> (PK_PRODUCT_PARTICIPANT)), PARTICIPANT_NAME INDEX (PK_PARTICIPANT_NAME)))
> Adapted plan:
> PLAN SORT (JOIN (JOIN (JOIN (TITLE INDEX (IDX_TITLE_METAPHONE_ASC),
> PRODUCT INDEX (IDX_PRODUCT_TITLEID)), PRODUCT_PARTICIPANT INDEX
> (PK_PRODUCT_PARTICIPANT)), PARTICIPANT_NAME INDEX (PK_PARTICIPANT_NAME)))
>
> TIA

Hi, I don't believe you (unless Firebird 2 is a revolution compared to
1.5.3)! The plan you state indicate that it is generated through using
AND, not OR. In general, CONTAINING and LIKE cannot use indexes (well,
if you use LIKE and a constant value (not a parameter) that doesn't
start with a wildcard, then an index can be used), so you'll never get
decent performance with ORing to these two. STARTING WITH or equality
comparisons is the way to go, and the rest of this reply refer to those.

Do you have an index IDX_TITLE_NAME_ASC? If so, it should be possible to
get something like

PLAN SORT (JOIN (JOIN (JOIN (TITLE INDEX (IDX_TITLE_METAPHONE_ASC,
IDX_TITLE_NAME_ASC), PRODUCT INDEX (IDX_PRODUCT_TITLEID)),
PRODUCT_PARTICIPANT INDEX (PK_PRODUCT_PARTICIPANT)), PARTICIPANT_NAME
INDEX (PK_PARTICIPANT_NAME)))

Though I'm confused by your LEFT JOIN, or rather doing an INNER JOIN to
a table that is on the right side of a LEFT JOIN.

If your problem is not a missing index on TITLE.NAME, then try "Adams
corrected suggestion" and see if that helps - maybe the optimizer gets
as confused as me...

Though don't forget, LIKE or CONTAINING cannot use indexes and will be
slow unless ANDed with something selective!

HTH,
Set