Hello Set,

Thanks for your reply! I've tried your query and it is indeed a bit faster. But there is still a big difference between MySQL and Firebird. The difference now is 900ms. What could be the cause?

The number of records:

Tree3 -> 343223 records
Article3_1 -> 42573 records

The plan of your query:

Prepare time: 0.003s
Field #01: TREE3.IDLINK Alias:IDLINK Type:INTEGER
Field #02: TREE3.IDTARGETLINK Alias:IDTARGETLINK Type:INTEGER
Field #03: TREE3.IDITEM Alias:IDITEM Type:INTEGER
Field #04: TREE3.NODEPOSITION Alias:NODEPOSITION Type:INTEGER
PLAN SORT (SORT (JOIN (TR INDEX (IDX_TREE3_3), ART INDEX (RDB$PRIMARY2))))

873306 fetches, 0 marks, 1961 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 380575 index, 0 seq.
Delta memory: 0 bytes.
Total execution time: 1.027s

Again thank you!

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >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
>