Hello,

I have a problem with the speed of a query in Firebird. The slowness is in the sorting and distinct.

If I try the query in MySQL then he is one second faster.

Firebird -> 1,3s a 1,6s
MySQL -> 0,3s a 0,4s

We use the Firebird database on a web server / website, so the speed is important.

Specification:
- Firebird 2.5.1 or 2.5.2 (SuperClassic) 64 bits
- 2,13 Ghz (2 processors)
- RAM 4,00 GB

What can I do?

I have the following tables:

====================================================

CREATE TABLE ARTICLE3_1
(
IDARTICLE Integer NOT NULL,
ITEMSTATUS Integer,
ITEMENTRYDATE Integer,
ITEMFILTER Integer,
ARTIKELNUMMER Varchar(250),
ARTIKELNAAM1 Varchar(250),
ARTIKELNAAM2 Varchar(250),
OMSCHRIJVING_DETAIL Blob sub_type 1,
OMSCHRIJVING1 Varchar(250),
OMSCHRIJVING2 Varchar(250),
ARTIKELNR_LEVERANCIER Varchar(250),
MERK Varchar(250),
LEVERANCIER Varchar(250),
EAN Varchar(250),
LINKAANGROEP Varchar(250),
LINKAANAANBIEDINGGROEP Varchar(250),
LINKAANPOPULAIRGROEP Varchar(250),
LINKAANART Varchar(250),
ARTGRPNR Varchar(250),
SUBGROEP Varchar(250),
PRIJSPER Integer,
VERKOOPPRIJS Float,
ADVIESPRIJS Float,
BTWPERC Float,
ONLINE Varchar(250),
TUSGROEPBIJLINK Varchar(250),
AFBEELDINGKLEIN Varchar(250),
AFBEELDINGMIDDEL Varchar(250),
AFBEELDINGGROOT Varchar(250),
ICECATLINK Varchar(250),
LINKAANHOMEPAGEGROEP Varchar(250),
LINKAANMIJNACCOUNTGROEP Varchar(250),
SORTEER Varchar(250),
AFBEELDING Varchar(100),
FLASH Blob sub_type 1,
EENHEID Varchar(250),
ALTARTNR1 Varchar(250),
ALTARTNR2 Varchar(250),
BESTELLENPER Float,
INFEED Varchar(250),
GOOGLE_TAXONOMIE Varchar(250),
FEED_TITEL Varchar(250),
FEED_OMSCHRIJVING Blob sub_type 1,
PRIMARY KEY (IDARTICLE)
);
CREATE INDEX IDX_ARTICLE3_1_2 ON ARTICLE3_1 (MERK);
CREATE INDEX IDX_ARTICLE3_1_3 ON ARTICLE3_1 (ARTIKELNUMMER);
CREATE INDEX IDX_ARTICLE3_1_4 ON ARTICLE3_1 (ARTIKELNR_LEVERANCIER);
CREATE INDEX IDX_ARTICLE3_1_5 ON ARTICLE3_1 (ALTARTNR2);
CREATE INDEX IDX_ARTICLE3_1_6 ON ARTICLE3_1 (ARTIKELNAAM1);
CREATE INDEX IDX_ARTICLE3_1_7 ON ARTICLE3_1 (EAN);

CREATE TABLE ARTICLE3_1
(
IDARTICLE Integer NOT NULL,
ITEMSTATUS Integer,
ITEMENTRYDATE Integer,
ITEMFILTER Integer,
ARTIKELNUMMER Varchar(250),
ARTIKELNAAM1 Varchar(250),
ARTIKELNAAM2 Varchar(250),
OMSCHRIJVING_DETAIL Blob sub_type 1,
OMSCHRIJVING1 Varchar(250),
OMSCHRIJVING2 Varchar(250),
ARTIKELNR_LEVERANCIER Varchar(250),
MERK Varchar(250),
LEVERANCIER Varchar(250),
EAN Varchar(250),
LINKAANGROEP Varchar(250),
LINKAANAANBIEDINGGROEP Varchar(250),
LINKAANPOPULAIRGROEP Varchar(250),
LINKAANART Varchar(250),
ARTGRPNR Varchar(250),
SUBGROEP Varchar(250),
PRIJSPER Integer,
VERKOOPPRIJS Float,
ADVIESPRIJS Float,
BTWPERC Float,
ONLINE Varchar(250),
TUSGROEPBIJLINK Varchar(250),
AFBEELDINGKLEIN Varchar(250),
AFBEELDINGMIDDEL Varchar(250),
AFBEELDINGGROOT Varchar(250),
ICECATLINK Varchar(250),
LINKAANHOMEPAGEGROEP Varchar(250),
LINKAANMIJNACCOUNTGROEP Varchar(250),
SORTEER Varchar(250),
AFBEELDING Varchar(100),
FLASH Blob sub_type 1,
EENHEID Varchar(250),
ALTARTNR1 Varchar(250),
ALTARTNR2 Varchar(250),
BESTELLENPER Float,
INFEED Varchar(250),
GOOGLE_TAXONOMIE Varchar(250),
FEED_TITEL Varchar(250),
FEED_OMSCHRIJVING Blob sub_type 1,
PRIMARY KEY (IDARTICLE)
);
CREATE INDEX IDX_ARTICLE3_1_2 ON ARTICLE3_1 (MERK);
CREATE INDEX IDX_ARTICLE3_1_3 ON ARTICLE3_1 (ARTIKELNUMMER);
CREATE INDEX IDX_ARTICLE3_1_4 ON ARTICLE3_1 (ARTIKELNR_LEVERANCIER);
CREATE INDEX IDX_ARTICLE3_1_5 ON ARTICLE3_1 (ALTARTNR2);
CREATE INDEX IDX_ARTICLE3_1_6 ON ARTICLE3_1 (ARTIKELNAAM1);
CREATE INDEX IDX_ARTICLE3_1_7 ON ARTICLE3_1 (EAN);
====================================================

The Query in FireBird:

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

The Query in MySQL:

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) AND ( (LCASE(art.Artikelnummer) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnummer) like 'a4' )
AND (LCASE(art.Artikelnummer) like 'papier')) OR (LCASE(art.Artikelnaam1) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam1) like '%a4%' )
AND (LCASE(art.Artikelnaam1) like '%papier%')) OR (LCASE(art.Artikelnaam2) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam2) like '%a4%' )
AND (LCASE(art.Artikelnaam2) like '%papier%')) OR (LCASE(art.Artikelnr_leverancier) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnr_leverancier) like '%a4%' )
AND (LCASE(art.Artikelnr_leverancier) like '%papier%')) OR (LCASE(art.Merk) like '%a4 papier%' ) OR ( (LCASE(art.Merk) like '%a4%' )
AND (LCASE(art.Merk) like '%papier%')) OR (LCASE(art.EAN) like '%a4 papier%' ) OR ( (LCASE(art.EAN) like '%a4%' )
AND (LCASE(art.EAN) like '%papier%')) OR (LCASE(art.AltArtnr1) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr1) like '%a4%' )
AND (LCASE(art.AltArtnr1) like '%papier%')) OR (LCASE(art.AltArtnr2) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr2) like '%a4%' )
AND (LCASE(art.AltArtnr2) like '%papier%')) ))
AND tr.NODELEVEL =5 and tr.LINKTYPE <> 5
ORDER BY tr.NodePosition LIMIT 30;

====================================================

I have the query executed with FlameRobin:

====================================================

Prepare time: 0.016s
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_2, IDX_TREE3_3), ART INDEX (RDB$PRIMARY2))))

873424 fetches, 0 marks, 12892 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 380580 index, 0 seq.
Delta memory: 1784 bytes.
Total execution time: 1.311s

====================================================

Thank you!