>PLAN SORT (SORT (JOIN (TR INDEX (IDX_TREE3_2, IDX_TREE3_3), ART INDEX
>(RDB$PRIMARY2))))
Hmm, I don't think you want the index for IDX_TREE3_2 to be used here. Being
the first table in the plan and noticing that Tr.iditem is only used in JOIN
... ON art.idarticle = tr.iditem, I think Firebird actually "changes" your
query to use tr.iditem > 0 rather than art.IDARTICLE > 0 and my hunch is that
most rows satisfy this criterion.
>SELECT FIRST 30 SKIP 0 distinct tr.IdLink, tr.IdTargetLink, tr.IdItem,
>tr.NodePosition
>FROM Tree3 tr
>inner join article3_1 art on art.idarticle = Tr.iditem
>WHERE tr.ItemType = 2 AND tr.Market1 = 1
>AND ((art.IDARTICLE > 0) AND ( (LOWER(art.Artikelnummer) like '%a4 papier%'
>) OR ( (LOWER(art.Artikelnummer) like 'a4' )
>AND (LOWER(art.Artikelnummer) like 'papier')) OR (LOWER(art.Artikelnaam1)
>like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam1) like '%a4%' )
>AND (LOWER(art.Artikelnaam1) like '%papier%')) OR (LOWER(art.Artikelnaam2)
>like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam2) like '%a4%' )
>AND (LOWER(art.Artikelnaam2) like '%papier%')) OR
>(LOWER(art.Artikelnr_leverancier) like '%a4 papier%' ) OR (
>(LOWER(art.Artikelnr_leverancier) like '%a4%' )
>AND (LOWER(art.Artikelnr_leverancier) like '%papier%')) OR (LOWER(art.Merk)
>like '%a4 papier%' ) OR ( (LOWER(art.Merk) like '%a4%' )
>AND (LOWER(art.Merk) like '%papier%')) OR (LOWER(art.EAN) like '%a4 papier%'
>) OR ( (LOWER(art.EAN) like '%a4%' )
>AND (LOWER(art.EAN) like '%papier%')) OR (LOWER(art.AltArtnr1) like '%a4
>papier%' ) OR ( (LOWER(art.AltArtnr1) like '%a4%' )
>AND (LOWER(art.AltArtnr1) like '%papier%')) OR (LOWER(art.AltArtnr2) like
>'%a4 papier%' ) OR ( (LOWER(art.AltArtnr2) like '%a4%' )
>AND (LOWER(art.AltArtnr2) like '%papier%')) ))
>AND tr.NODELEVEL =5 and tr.LINKTYPE <> 5
>ORDER BY tr.NodePosition
You use so many parenthesis that I had to restructure your query to easier
understand it. Doing that made me notice a minor error. I think the below query
is identical to your original query:
SELECT distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem
WHERE tr.ItemType = 2
AND tr.Market1 = 1
AND art.IDARTICLE+0 > 0
AND (LOWER(art.Artikelnummer) like '%a4 papier%'
OR (LOWER(art.Artikelnummer) like 'a4'
AND LOWER(art.Artikelnummer) like 'papier')
OR (art.Artikelnaam1 containing 'a4' AND art.Artikelnaam1 containing
'papier')
OR (art.Artikelnaam2 containing 'a4' AND art.Artikelnaam2 containing
'papier')
OR (art.Artikelnr_leverancier containing 'a4' AND art.Artikelnr_leverancier
containing 'papier')
OR (art.Merk containing 'a4' AND art.Merk containing 'papier')
OR (art.EAN containing 'a4' AND art.EAN containing 'papier')
OR (art.AltArtnr1 containing 'a4' AND art.AltArtnr1 containing 'papier')
OR (art.AltArtnr2 containing 'a4' AND art.AltArtnr2 containing 'papier'))
AND tr.NODELEVEL = 5
AND tr.LINKTYPE <> 5
ORDER BY tr.NodePosition
ROWS 1 TO 30
I didn't change art.Artikelnummer, to highlight that you don't have wildcards
for a4 and papier here. Change it to be like the others if you intended to have
wildcards, remove the OR statement entirely if not since it cannot be equal to
both a4 and papier. Elsewhere, I've generally ignored "like '%a4 papier%'"
since it is a subset of "like '%a4%' or like '%papier%'". Furthermore, I find
using CONTAINING to be simpler than using LOWER() or UPPER().
Unfortunately, excepting the addition of +0, I don't think the modifications
I've done helps you too much speedwise (but you should of course try it and
report the plan to this list). How many records and how large a proportion of
the records in Tree3 has Market1 = 1? Are there many duplicates if you remove
DISTINCT from your query?
If papier and a4 are fixed values to be used in this query, you may consider
adding a trigger populated Booleanish column (indexed) that you set to 1 if any
of the mentioned fields contain a4 or papier. I assume the number of rows
satisfying such a criteria to be far smaller than Market1 = 1.
Set
