markd_mms wrote:

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

Hi !

Since you are running FB 2.0 I am not sure how the new improvements on
the optimizer code will handle this, but give it a try

SELECT PRODUCT.PRODUCTID, PARTICIPANT_NAME.NAME, TITLE.NAME
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('dark side of the moon')
ORDER BY LEVENSHTEINC(TITLE.NAME, 'dark side of the moon')


On FB 1.5 and earlier an outer join forces the optimizer to use the
table as the first relation on the join order.... Really don't know how
it will go with FB 2.0 :-/

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br