The order by is causing the optimiser to start with the title table
and join back to the product table. You can discourage this by using
+0 on the FK index to title in product.

>
> SELECT PRODUCT.PRODUCTID, PARTICIPANT_NAME.NAME, TITLE.NAME
> FROM PRODUCT

Try replacing the following line:

> INNER JOIN TITLE ON PRODUCT.TITLEID = TITLE.TITLEID

With:

INNER JOIN TITLE ON PRODUCT.TITLEID+0 = 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('dark side of the moon')
> ORDER BY LEVENSHTEINC(TITLE.NAME, 'dark side of the moon')
>
> the plan is as follows...
>
> Plan:
> PLAN SORT (JOIN (PRODUCT NATURAL, TITLE INDEX (PK_TITLE),
> PRODUCT_PARTICIPANT INDEX (PK_PRODUCT_PARTICIPANT), PARTICIPANT_NAME
> INDEX (PK_PARTICIPANT_NAME)))
> Adapted plan:
> PLAN SORT (JOIN (PRODUCT NATURAL, TITLE INDEX (PK_TITLE),
> PRODUCT_PARTICIPANT INDEX (PK_PRODUCT_PARTICIPANT), PARTICIPANT_NAME
> INDEX (PK_PARTICIPANT_NAME)))
>
> the problem is that the query takes around 40 seconds to execute.
>
> if i move the TITLE and METAPHONE fields from the TITLE table to the
> PRODUCT TABLE and run the following query...
>
> SELECT PRODUCT.PRODUCTID, PARTICIPANT_NAME.NAME, PRODUCT.TITLE
> FROM PRODUCT
> INNER JOIN PRODUCT_PARTICIPANT ON PRODUCT.PRODUCTID =
> PRODUCT_PARTICIPANT.PRODUCTID
> INNER JOIN PARTICIPANT_NAME ON PRODUCT_PARTICIPANT.PARTICIPANTID =
> PARTICIPANT_NAME.PARTICIPANTID
> WHERE PRODUCT.METAPHONE = METAPHONE('dark side of the moon')
> ORDER BY LEVENSHTEINC(PRODUCT.TITLE, 'dark side of the moon')
>
> which has the following plan...
>
> Plan:
> PLAN SORT (JOIN (PRODUCT INDEX (IDX_PRODUCT_METAPHONE),
> PRODUCT_PARTICIPANT INDEX (PK_PRODUCT_PARTICIPANT), PARTICIPANT_NAME
> INDEX (PK_PARTICIPANT_NAME)))
> Adapted plan:
> PLAN SORT (JOIN (PRODUCT INDEX (IDX_PRODUCT_METAPHONE),
> PRODUCT_PARTICIPANT INDEX (PK_PRODUCT_PARTICIPANT), PARTICIPANT_NAME
> INDEX (PK_PARTICIPANT_NAME)))
>
> it takes about half a second to execute.
>
> is there any way of making the first query faster without having to
> moving the TITLE to the PRODUCT table?
>
> i'm running firebird 2.0 rc3 on windows xp.
>
> TIA
>