roberto06 roberto06 - 4 months ago 31
MySQL Question

Avoid multiple SELECT in WHERE clause

I'm trying to enhance a SQL bug I found in a merchant website.

I have two 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 to retrieve the products with the following features :


  • where color is either red or blue

  • where size is M

  • where fabric is cotton



My query is as follows :

SELECT p.id_product
FROM products p
WHERE p.id_product IN (SELECT f.id_product FROM features f WHERE f.feature_value IN ("Red", "Blue"))
AND p.id_product IN (SELECT f.id_product FROM features f WHERE f.feature_value = "M")
AND p.id_product IN (SELECT f.id_product FROM features f WHERE f.feature_value = "Cotton")
GROUP BY p.id_product


(Of course, in reality, my tables and my query are waaaay more complex than that, I'm just focusing on the problematic part)

The multiple SELECT in the WHERE clause causes my entire server to slow down if 8 or more features are selected.
Is there a way to avoid making this many queries in the WHERE clause ?

EDIT : For instance, here's one of the real queries :

SELECT p.id_product id_product
FROM 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 AND c.nleft >= 6 AND c.nright <= 7 AND c.active = 1)
LEFT JOIN ps_stock_available sa ON (sa.id_product = p.id_product AND sa.id_shop = 1)
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
WHERE 1
AND product_shop.active = 1
AND product_shop.visibility IN ("both", "catalog")
AND p.id_manufacturer IN (5,4)
AND sa.quantity > 0
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 82)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 37248)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 181)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 37821)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 33907)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 33902)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 70)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 76)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 291)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 75)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 44459)
GROUP BY id_product

Answer

You could use JOIN and HAVING clause:

SELECT p.id_product
FROM products p
JOIN features f
  ON p.id_product = f.id_product
GROUP BY p.id_product
HAVING COUNT(CASE WHEN f.feature_value IN ('Red', 'Blue') THEN 1 END) > 0
   AND COUNT(CASE WHEN f.feature_value = 'M' THEN 1 END) > 0
   AND COUNT(CASE WHEN f.feature_value = 'Cotton' THEN 1 END) > 0;

LiveDemo


Or even shorter (MySQL):

HAVING SUM(f.feature_value IN ('Red', 'Blue')) > 0
   AND SUM(f.feature_value = 'M') > 0
   AND SUM(f.feature_value = 'Cotton') > 0;