--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@t...> wrote:
[snip]

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

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.

> 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

TIA