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 [email protected], 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 >
