this is kind of related to my question last week about trying to avoid
large datasets when listing titles in a music and movie database. i
ended up using a combination of metaphones (like a soundex) and
levenshtein distance.

i need to show a list of artists and titles where the metaphone
matches the metaphone of the title someone's entered. the tables i'm
using for this query look a bit like this (the relevant parts anyway)...

PRODUCT
-------
PRODUCTID (PK)
TITLEID

TITLE
-----
TITLEID (PK)
NAME (ASC IDX)
METAPHONE (ASC IDX)

PRODUCT_PARTICIPANT
-------------------
PRODUCTID (PK)
PARTICIPANTID (PK)

PARTICIPANT_NAME
----------------
PARTICIPANTID (PK)
NAME (ASC IDX)

and the query (at the moment) looks like this...

SELECT PRODUCT.PRODUCTID, PARTICIPANT_NAME.NAME, TITLE.NAME
FROM PRODUCT
INNER JOIN TITLE 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('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