Vincent_L

FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?

Discussion created by Vincent_L on Jul 1, 2014
Latest reply on Jul 2, 2014 by DamianKelly

Hi,

 

This is fast, 0,9s

 

SELECT DISTINCT A."key" FROM checklogs_records A

JOIN products_to_categories B

ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

LEFT OUTER JOIN products_matching C

ON B.Product_ERP=C.product_erp

 

WHERE

A.d_val001='28/06/2014' AND

A.checklog_type_id=5

 

 

This is super slow, 226 s

 

SELECT DISTINCT A."key" FROM checklogs_records A

JOIN products_to_categories B

ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

LEFT OUTER JOIN products_matching C

ON B.Product_ERP=C.product_erp AND C.matching_strength=1 AND C.flux_id=1

 

WHERE

A.d_val001='28/06/2014' AND

A.checklog_type_id=5

 

 

The fields C.matching_strength=1 AND C.flux_id=1 are totally indexed. In fact, all the fields involved are indexed

 

I don't understand at all why is so much slower, any ideas ?

Outcomes