markd_mms wrote:

>Running this query takes 3 seconds:
>
>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
>LEFT JOIN ARTISTITEM ON ITEM.ITEMID = ARTISTITEM.ITEMID
>LEFT JOIN ARTISTS ON ARTISTITEM.ARTISTID = ARTISTS.ARTISTID
>LEFT JOIN ITEMINFORMAT ON ITEM.ITEMID = ITEMINFORMAT.ITEMID
>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'
>
>

When when use a "right" table field used in a left join in the where
clause and is not a null comparision is just as if it is an inner join.

>but if I add
>
>AND
>ITEM.TITLE CONTAINING 'knight'
>
>
>then it only takes ~700ms. Looking at the plan analyser shows 14010
>unindexed reads on the ITEM table, which is the number of records in
>the table. Every selected field in the query is indexd. The plan is as
>follows:
>
>

with this criteria in the where clause the optimizer could filter on
table Item (probably using an index), without this criteria and with all
left joins all rows in the Item Table should be scanned.

>Is there any way of speeding this up?
>
>

try this one:

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

>TIA
>
>
>

see you !

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