markd_mms wrote:

>--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
><iblist@t...> wrote:
>
>
>Plan is as follows...
>
>PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEMINFORMAT NATURAL,ITEM
>INDEX (PK_ITEM)),ARTISTITEM INDEX (IDX_ARTISTITEM_ITEMID)),ARTISTS
>INDEX (PK_ARTISTS)),FORMAT INDEX (PK_FORMAT)),DISTRIBUTOR INDEX
>(PK_DISTRIBUTOR)),GENRE INDEX (PK_GENRE)):
>PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEMINFORMAT NATURAL,ITEM
>INDEX (PK_ITEM)),ARTISTITEM INDEX (IDX_ARTISTITEM_ITEMID)),ARTISTS
>INDEX (PK_ARTISTS)),FORMAT INDEX (PK_FORMAT)),DISTRIBUTOR INDEX
>(PK_DISTRIBUTOR)),GENRE INDEX (PK_GENRE))
>
>Adapted plan:
>PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEMINFORMAT NATURAL,ITEM
>INDEX (PK_ITEM)),ARTISTITEM INDEX (IDX_ARTISTITEM_ITEMID)),ARTISTS
>INDEX (PK_ARTISTS)),FORMAT INDEX (PK_FORMAT)),DISTRIBUTOR INDEX
>(PK_DISTRIBUTOR)),GENRE INDEX (PK_GENRE))
>
>
>
>>try this one:
>>
>>
>[snip]
>
>
>>but this will be slow again :-(
>>
>>
>
>151 ms with 12 matches.
>
>

I think this time is for the the query before that phrase (but this will
be slow again)

>
>
>>SELECT ITEM.TITLE AS "Title", ITEM.TITLE2 AS "Title2",
>>ITEMINFORMAT.CATALOGUENO AS "Catalogue Number",
>>ITEMINFORMAT.BARCODE AS "Barcode", FORMAT.DESCRIPTION AS "Format",
>>DISTRIBUTOR.DESCRIPTION AS "Distributor", GENRE.DESCRIPTION AS "Genre",
>>ITEM.DISTRIBUTOR_RELEASE_DATE AS "Release Date", ITEM.ITEMID
>>FROM ITEM
>>JOIN ITEMINFORMAT ON ITEM.ITEMID + 0 = ITEMINFORMAT.ITEMID
>>LEFT JOIN ARTISTITEM ON ITEM.ITEMID = ARTISTITEM.ITEMID
>>LEFT JOIN ARTISTS ON ARTISTITEM.ARTISTID = ARTISTS.ARTISTID
>>LEFT JOIN FORMAT ON ITEMINFORMAT.FORMATID = FORMAT.FORMATID
>>LEFT JOIN DISTRIBUTOR ON ITEM.DISTRIBUTORID = DISTRIBUTOR.DISTRIBUTORID
>>LEFT JOIN GENRE ON ITEM.GENREID = GENRE.GENREID
>>WHERE
>>ITEMINFORMAT.CATALOGUENO = '8236709'
>>
>>
>
>1.5 seconds
>
>
>

as expected...

I think you indices statistics are out of date, so the optimizer could
not find the better approach.

Another consideration.
Do you need all the outer joins ? The table slookslike normal linked
tables, like Item and ItemInFormat, there is a possibility that you have
an Item without a format specified ? (and so on...)

>TIA
>
>
>
>

see you !

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