MySQL query optimization help

Hi All,

Below is a query that I am running and it is kind of slow (30 + secs).  Is there a way to optimize it.  Also the query will be incorrect for the month of January, if the franchise did not post anything:

CREATE TEMPORARY TABLE GrossRev1

SELECT c.ID, IFNULL(R.GrossRevenue,0) AS GROSSREVENUE, IFNULL(R.RoyaltiesOwed,0) ROYALTIESOWED, 
IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS, IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS, 
IFNULL(Total_hours,0) TOTAL_HRS, c.year,c.month
FROM 
  (SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a, 
  (SELECT DISTINCT year, month FROM royalties WHERE year = 2015 
   ) as b) as c 
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month

LEFT JOIN (SELECT h.franchiseno AS fn, h.Month, h.year,  
            MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week1,
            MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week2,
            MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week3,
            MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week4,
            MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week5,
            CAST(SUM(h.hoursbilled) AS DECIMAL(30,2)) AS Total_hours
            FROM
            hours_log h  
            WHERE h.Year = 2015 -- and h.month = 9
            GROUP BY
            h.franchiseno,h.reportmonth, h.reportyear) h1 ON h1.fn = R.franchiseno AND h1.ReportMonth = R.Month AND h1.year = R.year            
WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00') 
ORDER BY c.year DESC, c.month DESC,c.ID ASC;


SELECT GR.*, 
  CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.GROSSREVENUE
  ELSE GR.GROSSREVENUE END CURRENT_MONTH_FORECAST_AGR,
  CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN  R.RoyaltiesOwed
  ELSE GR.ROYALTIESOWED END CURRENT_MONTH_FORECAST_TOTAL_ROYALTIES

FROM GrossRev1 GR
LEFT JOIN (SELECT RL.franchiseno, RL.GROSSREVENUE, RL.RoyaltiesOwed, RL.year, RL.month
		   FROM royalties_log RL 
           ) R ON R.franchiseno = GR.ID AND R.year = GR.year AND R.month = GR.month - 1;

DROP TEMPORARY TABLE GrossRev1;

Select all Open in new window

Thank you for your help in advance