markd_mms wrote:

>wow down to 141 ms! but now searching on ITEM.TITLE by itself has gone
>up to 1.5 seconds from about 300ms showing 14010 unindexed reads on
>ITEMINFORMAT. is there a happy medium or should i just use a different
>query depending on what i'm searching for?
>
>TIA
>
>
>
>

What's the plan for the query when you filter on Item.Title ?

The frst query use all left joins, the optimizer are able to try
alternate join orders for the tables found until the first outer join,
so in your first query, if you filter on Item.Title an index would be
used, now that you have two tables before the first outer join, perhaps
the otimizer was not using the index on title because it thinks another
plan is a better choice, but all is wild guess without seeing the plan.

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 + 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
ITEM.Title = 'Star Wars'

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'



see you !

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