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 ;