MySQL Forums
Forum List  »  Newbie

Is this query optimized properly?
Posted by: Bodi K
Date: January 26, 2015 02:50PM

Hi, thanks for reading.

I'm getting a result that takes about 0.4 seconds to run. I feel like that's a lot! I have indexes on everything and I'm pretty sure my joins are perfect, so I'm wondering if there's anything I'm missing...

select
`savourys_customer`.`recipe`.`ID` AS `RecipeID`,
`nutrient_view`.`Nutr_No` AS `Nutr_No`,
`nutrient_view`.`NutrDesc` AS `NutrDesc`,
sum((`nutrient_view`.`Value` * `savourys_customer`.`recipe-ingredient`.`Grams`)) AS `Value`,
`nutrient_view`.`Units` AS `Units`,
`nutrient_view`.`Male` AS `Male`,
`nutrient_view`.`Female` AS `Female`,
(sum((`nutrient_view`.`Value` * `savourys_customer`.`recipe-ingredient`.`Grams`)) / `nutrient_view`.`Male`) AS `MDV`,
(sum((`nutrient_view`.`Value` * `savourys_customer`.`recipe-ingredient`.`Grams`)) / `nutrient_view`.`Female`) AS `FDV`,
`nutrient_view`.`Importance` AS `Importance`
from
(`savourys_customer`.`recipe`
join (`savourys_customer`.`nutrient_view`
join `savourys_customer`.`recipe-ingredient` ON ((`nutrient_view`.`ID` = `savourys_customer`.`recipe-ingredient`.`IngredientID`))) ON ((`savourys_customer`.`recipe`.`ID` = `savourys_customer`.`recipe-ingredient`.`RecipeID`)))
group by `savourys_customer`.`recipe`.`ID` , `savourys_customer`.`recipe`.`RecipeName` , `nutrient_view`.`Nutr_No` , `nutrient_view`.`NutrDesc` , `nutrient_view`.`Units` , `nutrient_view`.`Male` , `nutrient_view`.`Female`
having (sum((`nutrient_view`.`Value` * `savourys_customer`.`recipe-ingredient`.`Grams`)) > 0)
order by `savourys_customer`.`recipe`.`ID` , `nutrient_view`.`NutrDesc`


***** EXPLAIN ***
1 PRIMARY recipe-ingredient ALL IngredientID,RecipeID 4349 Using temporary; Using filesort
1 PRIMARY recipe eq_ref PRIMARY PRIMARY 4 savourys_customer.recipe-ingredient.RecipeID 1
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 4 savourys_customer.recipe-ingredient.IngredientID 10
2 DERIVED food_desc ALL NDB_No 330 Using where
2 DERIVED nut_data ref IDX_NDB_No_Nutr_No,Nutr_No IDX_NDB_No_Nutr_No 15 savourys_usda.food_desc.NDB_No 41
2 DERIVED nutr_def eq_ref Nutr_No Nutr_No 2 savourys_usda.nut_data.Nutr_No 1

************* CREATE TABLES ****


CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `savourys_customer`.`nutrient_view` AS
select
`savourys_usda`.`food_desc`.`ID` AS `ID`,
`savourys_usda`.`food_desc`.`NDB_No` AS `NDB_No`,
`savourys_usda`.`nutr_def`.`Nutr_No` AS `Nutr_No`,
`savourys_usda`.`food_desc`.`Long_Desc` AS `Long_Desc`,
`savourys_usda`.`nutr_def`.`NutrDesc` AS `NutrDesc`,
`savourys_usda`.`nut_data`.`Nutr_Val` AS `Value`,
`savourys_usda`.`nutr_def`.`Units` AS `Units`,
`savourys_usda`.`nutr_def`.`Male` AS `Male`,
`savourys_usda`.`nutr_def`.`Female` AS `Female`,
`savourys_usda`.`nutr_def`.`Importance` AS `Importance`
from
((`savourys_usda`.`nutr_def`
join `savourys_usda`.`nut_data` ON ((`savourys_usda`.`nutr_def`.`Nutr_No` = `savourys_usda`.`nut_data`.`Nutr_No`)))
join `savourys_usda`.`food_desc` ON ((`savourys_usda`.`nut_data`.`NDB_No` = `savourys_usda`.`food_desc`.`NDB_No`)))
having (`savourys_usda`.`nutr_def`.`Importance` = 0)




CREATE TABLE IF NOT EXISTS `event-ingredient` (
`EventID` int(10) unsigned NOT NULL,
`IngredientID` smallint(5) unsigned NOT NULL,
`Grams` decimal(6,2) unsigned NOT NULL,
`SpecificID` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`EventID`,`IngredientID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `event-recipe` (
`EventID` int(10) unsigned NOT NULL,
`RecipeID` int(10) unsigned NOT NULL DEFAULT '0',
`Servings` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`EventID`,`RecipeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `recipe` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`RecipeName` varchar(80) NOT NULL,
`DateCreated` date NOT NULL,
`DateModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`DatePublished` date DEFAULT NULL,
`CreatorID` int(10) unsigned NOT NULL,
`Description` varchar(250) DEFAULT NULL,
`CategoryID` tinyint(3) unsigned NOT NULL DEFAULT '0',
`SourceID` tinyint(3) unsigned NOT NULL DEFAULT '0',
`PrepTime` smallint(5) unsigned DEFAULT NULL,
`CookTime` smallint(5) unsigned DEFAULT NULL,
`Servings` smallint(4) unsigned NOT NULL DEFAULT '1',
`Scalable` tinyint(1) unsigned NOT NULL,
`CopyID` int(10) unsigned NOT NULL,
`Privacy` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '0 - Me only, 1 - Friends, 2 - Public',
`DefaultPrice` decimal(5,2) unsigned NOT NULL,
`DefaultImage` varchar(200) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `CategoryID` (`CategoryID`),
KEY `CreatorID` (`CreatorID`),
KEY `Privacy` (`Privacy`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=900 ;



CREATE TABLE IF NOT EXISTS `recipe-ingredient` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`RecipeID` int(10) unsigned NOT NULL,
`IngredientID` int(10) unsigned NOT NULL,
`Grams` decimal(6,2) unsigned NOT NULL,
`UnitID` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT 'Default measurement used for this ingredient',
PRIMARY KEY (`ID`),
KEY `Ingred` (`RecipeID`,`IngredientID`),
KEY `IngredientID` (`IngredientID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4400 ;

Options: ReplyQuote


Subject
Written By
Posted
Is this query optimized properly?
January 26, 2015 02:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.