Query firebird slow order by / distinct

Question

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 TREE3
(
  IDLINK Integer NOT NULL,
  LINKTYPE Integer,
  IDITEM Integer,
  ITEMTYPE Integer,
  IDTARGETLINK Integer,
  NODEPOSITION Integer,
  NODELEVEL Integer,
  IDLAYOUTDATA Integer,
  IDTEMPLATE Integer,
  ACTIONDATE Integer,
  MARKET1 Integer,
  PRIMARY KEY (IDLINK)
);
CREATE INDEX IDX_TREE3_2 ON TREE3 (IDITEM);
CREATE INDEX IDX_TREE3_3 ON TREE3 (MARKET1);
CREATE INDEX ITREE13 ON TREE3 (IDTARGETLINK,NODEPOSITION);
CREATE INDEX ITREE53 ON TREE3 (IDITEM,ITEMTYPE);

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

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!

Markswoman

What's tree3? You listed the article3_1 table twice. – Etymology

Sorry, I had two times the same metadata added. – Markswoman

Answer 1

Yip avoid DISTINCT and LIKE if you can, DISTINCT Optimization http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Try a nested query with group by instead of distinct. I use this to get around the issue when using group by & order by.

select * from ({the rest of the query}) as some_table group by {my distinct column};

Also I can't see your table engine, but MyIsam is better for full text searching (rather than InnoDB). Also it might be worth looking at Solr for full text searching. A bit of a learning curve to set up, but you can index mysql tables then perform partial match searches across multiple columns. With things like boosting and awesomeness.

See if the below query has any performance benefits.

select * from (SELECT 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)
as some_table group by IdLink;

Overturn

Answer 2

This may be a bit old now but hopefully can still be helpful.

Generally speaking, distinct and order by operations require sort. Sorts are aided by indexes. Consider creating indexes for the columns specified in order by clause - NodePosition, the only other index on it I can see is composite with another column so that index is not consulted by the order by. For the distinct, you may try creating a composite index for tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition columns or each individually. (I am not too sure on how much distinct would be helped by indexes but worth a try).

Other things to consider: your where clause uses functions - functions when used in this context will result in full table scans and may not even look at your indexes. I don't believe mySql supports function-based indexes, not sure about FireBird. But it can be worked around by creating another column that can hold the result of the LOWER(column), you would need to maintain that column using triggers if available.

OR condition and LIKE '%a4%' will also result in full table scans. I realize that your business logic may not allow you to drop the wildcard char from the beginning of the '%a4%' string so to possibly improve such use cases, you may consider subqueries - first try to narrow down your result set as much as possible in the subquery avoiding any LIKE or OR and then wrap that result with a parent query which will further filter the results (putting subquery into the FROM clause). So in your subquery, you'd have these conditions: tr.ItemType = 2 AND tr.Market1 = 1 and tr.NODELEVEL =5 and tr.LINKTYPE <> 5

Moschatel

Yes, Firebird 2+ allows indexes on expressions/functions: firebirdsql.org/refdocs/… – Megasporophyll