Re: Is this query optimized properly?

[Phillip Ward]

I would strongly recommend:

(1) Lose the database names.
Issue a "use savourys_customer" statement immediately after connecting. Including the database/ schema name in your queries reduces the portability of your query; you can't run it in, say, a test database, because it's hard-wired to use the live one.

(2) Lose as many braces as your sanity will allow.

(3) Lose the braces that start immediately after the "from" keyword.
By wrapping everything in the "from" inside brackets, you're telling MySQL that the data its reading is a "derived table" - one that's built on-the-fly, in memory (which is why you're seeing "DERIVED" in the explain plan).
Most significantly:

From: http://www.percona.com/blog/2006/08/31/derived-tables-and-views-performance/

Quote

Derived Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example).

So MySQL is going to build an "in-memory" table containing all of this data (and, sorry to say, that's exactly what you were doing with all those extraneous brackets!). It doesn't have to - it can read the data directly, so long as you let it!

So; with a little more trimming, a few correlation names dotted in here and there and you get something like this:

select 
  r.ID AS RecipeID 
, nv.Nutr_No 
, nv.NutrDesc 
, sum( nv.Value * ri.Grams ) AS Value 
, nv.Units 
, nv.Male 
, nv.Female 
, sum( nv.Value * ri.Grams ) / nv.Male AS MDV
, sum( nv.Value * ri.Grams ) / nv.Female AS FDV 
, nv.Importance 
from 
           recipe  r 
inner join recipe_ingredient  ri 
      on   r.ID = ri.RecipeID 
inner join nutrient_view  nv 
      on   ri.IngredientID = nv.ID

I've left out the extremely dubious "group by" clause you had in there - anything that isn't aggregated (sum(), count(), etc) must be included in the "group by" clause or you will get some very odd results. You might be better off extracting a simpler, grouped and summed data set of just the ID's and totals, and then joining that to the other tables to add the names and descriptions later on.

Regards, Phill W.