mysql - Avoid multiple SELECT in WHERE clause -


i'm trying enhance sql bug found in merchant website.

i have 2 tables :

------ table "products" ------ | id_product | product_name | ----------------------------- |          1 |      t-shirt | |          2 |     trousers | |          3 |  sweat-shirt | |          4 |        socks |  ----------- table "features" ----------- | id_product | feature | feature_value | ---------------------------------------- |          1 |   color |           red | |          1 |    size |             m | |          1 |  fabric |        cotton | |          2 |   color |          blue | |          2 |    size |             s | |          2 |  fabric |     polyester | |          3 |   color |           red | |          3 |    size |             l | |          3 |  fabric |          wool | |          4 |   color |         white | |          4 |    size |             l | |          4 |  fabric |        cotton |

i'm trying retrieve products following features :

  • where color either red or blue
  • where size m
  • where fabric cotton

my query follows : 

select p.id_product products p p.id_product in (select f.id_product features f f.feature_value in ("red", "blue"))     , p.id_product in (select f.id_product features f f.feature_value = "m")     , p.id_product in (select f.id_product features f f.feature_value = "cotton") group p.id_product

(of course, in reality, tables , query waaaay more complex that, i'm focusing on problematic part)

the multiple select in clause causes entire server slow down if 8 or more features selected. there way avoid making many queries in clause ?

edit : instance, here's 1 of real queries : 

select p.id_product id_product ps_product p  inner join ps_category_product cp on p.id_product = cp.id_product  inner join ps_category c on (c.id_category = cp.id_category , c.nleft >= 6 , c.nright <= 7 , c.active = 1) left join ps_stock_available sa on (sa.id_product = p.id_product , sa.id_shop = 1)  inner join ps_product_shop product_shop on (product_shop.id_product = p.id_product , product_shop.id_shop = 1) 1 , product_shop.active = 1 , product_shop.visibility in ("both", "catalog") , p.id_manufacturer in (5,4) , sa.quantity > 0 , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 82) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 37248) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 181) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 37821) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 33907) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 33902) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 70) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 76) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 291) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 75) , p.id_product in (select id_product ps_feature_product fp fp.id_feature_value = 44459) group id_product

you use join , having clause:

select p.id_product products p join features f   on p.id_product = f.id_product group p.id_product having count(case when f.feature_value in ('red', 'blue') 1 end) > 0    , count(case when f.feature_value = 'm' 1 end) > 0    , count(case when f.feature_value = 'cotton' 1 end) > 0;

livedemo

or shorter (mysql):

having sum(f.feature_value in ('red', 'blue')) > 0    , sum(f.feature_value = 'm') > 0    , sum(f.feature_value = 'cotton') > 0;

Comments

Post a Comment