Enhancing performance when applying multiple joins in mysql

Question

I have 3 tables:

Nutritions_facts table which is static table that have all nutrition nutrition_id and nutrition_ename Products_info table which has products info and the columns are product_id, product_ename, brand product_nutrition_facts which is mediator table that links the product with its nutrition facts with their values . The structure is product_id, nutrition_id, nutrition_value .. Each product can have 1 row or more depending on number of nutrition facts it has. Here is a real testing example

Nutrition_facts table

 nutrition_id |nutrition_ename
       1 | caloreis    
       2 | fat    
       3 | sugar    
       4 | salt

Products_info table

product_id| product_ename           | brand   
    1 | Nutella Hazelnut Cocoa | Nutella    
    2 | Nutella Jar            | Nutella

product_nutrition_facts table

product_id | nutrition_id | nutrition_value
     1 |            1 |             200
     1 |            2 |              15
     1 |            3 |               2
     1 |            4 |              11
     2 |            1 |             200
     2 |            2 |              15
     2 |            3 |              12
     2 |            4 |              11

I need to make query that returns me the products' name with value of sugar is less than or equla 15 and salt less than or equal 140

I build a query that return correct values but it takes long time to process. Can someone suggest edits to enhance the performance please..

SELECT DISTINCT p.product_id, p.brand, p.e_name, p.image_low
FROM products_info p
JOIN product_nutrition_facts pn ON p.product_id = pn.product_id
WHERE p.brand =  'AL MARAI'
AND (
(
p.product_id
IN (

SELECT product_id
FROM product_nutrition_facts pn
WHERE pn.nutrition_id =3
AND pn.nutrition_value <=15
)
 )
 AND (
 p.product_id
IN (

SELECT product_id
FROM product_nutrition_facts pn
WHERE pn.nutrition_id =4
AND pn.nutrition_value <=140
)
)
)
AND (
pn.nutrition_id =3
OR pn.nutrition_id =4
)

EDITS

 CREATE TABLE `products_info` (
 `product_id` int(11) NOT NULL AUTO_INCREMENT,
 `image_low` varchar(400) DEFAULT NULL,
  `e_name` varchar(200) DEFAULT NULL, 
 PRIMARY KEY (`product_id`),
 UNIQUE KEY `product_id_UNIQUE` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=249292 DEFAULT CHARSET=utf8


CREATE TABLE `product_nutrition_facts` (
 `prod_nut_id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) DEFAULT NULL,
 `nutrition_id` int(11) DEFAULT NULL,
 `nutrition_value` varchar(25) DEFAULT NULL,
 `unit_id` int(11) DEFAULT NULL,
 `parent` int(11) DEFAULT NULL,
 `serving_size` varchar(145) DEFAULT NULL,
 `serving_size_unit` int(11) DEFAULT NULL,
 `no_nutrition_facts` int(11) NOT NULL,
 `added_by` varchar(145) DEFAULT NULL,
 `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `inserted_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_by` varchar(150) NOT NULL,
 PRIMARY KEY (`prod_nut_id`),
 UNIQUE KEY `prod_nut_id_UNIQUE` (`prod_nut_id`),
 KEY `nutrition_id_fk_idx` (`nutrition_id`),
 KEY `unit_Fk_idx` (`unit_id`),
 KEY `unit_fk1_idx` (`serving_size_unit`),
 KEY `product_idFK` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=580809 DEFAULT CHARSET=utf8

 CREATE TABLE `nutrition_facts` (
 `nutrition_id` int(11) NOT NULL AUTO_INCREMENT,
 `nutrition_aname` varchar(145) DEFAULT NULL,
 `nutrition_ename` varchar(145) DEFAULT NULL,
 `alternative_name` varchar(145) DEFAULT NULL,
  `unit` varchar(8) NOT NULL,
 `daily_value` int(11) NOT NULL,
 `nut_order` int(2) NOT NULL,
 `is_child` int(1) NOT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`nutrition_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8

add tables description show create table Nutritions_facts, show create table Products_info, show create table product_nutrition_facts — Max P.
– Max P., Commented Feb 10, 2016 at 10:48
@MaxP. I add them but products_info table is very big so I put just part of it.. — palAlaa
– palAlaa, Commented Feb 10, 2016 at 11:12

Max P. · Accepted Answer · 2016-02-10 11:19:02Z

1

Try to add indexes product_nutrition_facts (nutrition_id,nutrition_value,product_id), product_nutrition_facts (product_id,nutrition_id,nutrition_value), products_info (brand) and perfom query

SELECT p.*
FROM products_info p
join product_nutrition_facts pn1 on
  pn1.product_id=p.product_id
  AND pn1.nutrition_id=3
  AND pn1.nutrition_value<=15
join product_nutrition_facts pn2 on
  pn2.product_id=p.product_id
  AND pn2.nutrition_id=4
  AND pn2.nutrition_value<=140
where
  p.brand =  'AL MARAI'

answered Feb 10, 2016 at 11:19

Max P.

5,6992 gold badges17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2016-02-11 23:12:47Z

0

IN ( SELECT ... ) -- Does not optimize well; turn into JOIN (as Max suggests)

"Overnormalization" -- Nutrition_facts can be eliminated; simply use the nutrition_names in place of nutrition_id.

answered Feb 11, 2016 at 23:12

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Enhancing performance when applying multiple joins in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related