We help IT Professionals succeed at work.
Get Started

MySQL query optimization help

thomasm1948
thomasm1948 asked
on
76 Views
Last Modified: 2015-10-21
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;

Open in new window


Thank you for your help in advance
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 34 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE