MySQL query optimization help

Looks like the delay comes from the many calculations involved.

I helped in one question involving the franchise, and royalties tables.
If you can rephrase the question using these two existing tables, I might be able to help.

In the meantime, one needs to find out which part is causing the delay

there are 2 queries, is it the first one that takes 30+ seconds??

Have you done an EXPLAIN on the slow query?

         EXPLAIN select ....

This is your first tool to help, and it helps us too, so you should do one and provide the result here please.

===
please please please use the explicit "CROSS JOIN" when forming a deliberate Cartesian product (which you are doing as alias c) it will help make your queries more maintainable.

Can you explain what is the purpose of this:

select * 
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

Select allOpen in new window

It seems to me a totally unnecessary complication to cross join distinct month from royalty to franchise and then join again the whole thing to royalty. That has to be simplified to simple joins with ON clauses.

Make sure you have the followingg indexes:

franchises - closeddate

royalties - Year, Month

hours_log -  Year, franchiseno, reportmonth, reportyear

Another question related to the same pice of code.

You build that cross joined  table c and then you left join with R(royalties) on c.ID = R.franchiseno. However

c.ID comes from a.ID, which is franchise table, which also has franchise_no. Why do you join royalties.franchiseno with franchise.ID  instead of franchise.franchise_no?

That whole query needs refactored. It si very badly written.

@Zberteoc,
The Cross Join and then Left join was a solution to another question by the same author.
https://www.experts-exchange.com/questions/28744465/MySQL-Left-join-question.html

The author is building on that question.

Properly understanding the current question may lead to some modifications.

@thomasm1948
Can you type few records, and list the required output. Supply the new table structures and the insert statements of the test records.

Below is a description for the code:

CREATE TEMPORARY TABLE GrossRev1

Dump the query in a temporary table.  The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for a month.  They use it for forecasting

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

This portion allows me to do a left join and fill in the year and month that a franchisee did not post anything.  If I do not use this then when I run the query  for multiple months then the report does not show exactly which month the franchisee did not post to

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


I join on franchises.id because the franchise_no  (this is the actual franchise identification) but the royalty table and hours_log table use the id not the franchise_no…I do not know why they designed it this way.

LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month


This is the hours_log.  This is where the franchisee post their weekly hours in a single table.  Once again the franchiseno is actually the ID field in the franchises table.  I need to show all of weeks in a single row for the year and month.  Each record in the table list only the year, month and week number.

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;

The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for the current month.  They use it for forecasting.  I could not think of another way to get this info.

I am stuck at this point as well because January’s calculation will be incorrect do to that it must know find the previous year and Decembers (12) grossrev and royalties


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;

Thank you for all of your help

I am redoing the query using temp table and test data real quick to give a n understanding of what is used.  This env does not have a test or dev env

ok this should better explain what I am trying to do

test tables and data

CREATE TABLE franchise ( 
     FIN_NUM INT,
     Franchise_no INT,
     Business_Entity varchar (50)
    ); 
    CREATE TABLE Royalties (
     FIN_NUM INT,
     GrossRev DECIMAL(30,2),     
     RMonth INT,
     RYear Int
    );  
    CREATE TABLE hours_log (
     FIN_NUM INT,     
     Hours INT,     
     RMonth INT,
     RYear INT,
     reportweek INT
    );   
     -- insert data franchise
	INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0001, 101, 'test1');
	INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0002, 102, 'test2');
	INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0003, 103,'test3');
     -- insert data royalty
	INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 500, 12, 2014);    
    INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 2000, 2, 2015);
    INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 2500, 2, 2015);
    INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 4000, 1, 2015);
    INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 2000, 1, 2015);
    INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 1000, 3, 2015);
    -- insert data hours_log
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 10, 12, 2014, 1);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 20, 12, 2014, 2);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 3);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 4);    
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 1);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 2);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 3);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 4);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 1);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 2);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 45, 2, 2015, 3);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 47, 2, 2015, 4);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 50, 2, 2015, 1);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 65, 2, 2015, 2);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 55, 2, 2015, 3);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 70, 2, 2015, 4);
    
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 1);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 30, 1, 2015, 2);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 3);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 4);
    
	INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 10, 3, 2015, 1);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 20, 3, 2015, 2);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 3);
    INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 4);
        
   

Select allOpen in new window

report

CREATE TEMPORARY TABLE test1
SELECT c.FIN_NUM, c.Franchise_no, IFNULL(R.grossrev,0.00) As grossrev, 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.ryear, c.rmonth
FROM 
  (SELECT a.FIN_NUM, a.Franchise_no, b.ryear,b.rmonth FROM franchise as a, 
  (Select distinct ryear, rmonth from royalties) as b) as c     
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth

LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,  
            MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
            MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
            MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
            MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
            MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
            CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
            FROM
            hours_log h  
            WHERE h.rYear = 2015 
            GROUP BY
            h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear

order by c.ryear DESC, c.rmonth DESC,c.FIN_NUM ASC;

SELECT GR.franchise_no, GR.GROSSREV, GR.WEEK1_HRS, GR.WEEK2_HRS, GR.WEEK3_HRS, GR.WEEK4_HRS, 
GR.WEEK5_HRS, GR.TOTAL_HRS, GR.ryear,GR.rmonth, 
  
  CASE WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
  
  ELSE GR.GROSSREV END FORECAST_GR
  
-- R.AdjGrossRevenue, R.RoyaltiesOwed
FROM test1 GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
		   FROM royalties RL 
           ) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by GR.franchise_no, GR.ryear asc, GR.rmonth;
DROP TEMPORARY TABLE test1;

Select allOpen in new window

Do you have the indexes i suggested you?

No the database that I am working does not.  I recommended them but I was told do not put them in.  There are no FKs as well.

This is a new company that I am working for and I cannot use SPs as well.  So I am trying to work within the parameters that they have given me

Well, that is your answer. If you don't have the right indexes there is no way you can get performance. Can at least they give you a list with the indexes that exits on those 3 tables?

Tell me how long it takes to run only this query:

SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,  
            MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
            MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
            MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
            MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
            MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
            CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
            FROM
            hours_log h  
            WHERE h.rYear = 2015 
            GROUP BY
            h.FIN_NUM,h.rmonth, h.ryear

Select allOpen in new window

I am not worried about FKs, indexes matter here!

the only ones that I see is:

Royalties Table:
1 rmonth
2 Franchiseno, ryear, rmonth

Hours_log:
no indexes

Franchise:
1. franchise_no

This is a new company that I am working for and I cannot use SPs as well.  So I am trying to work within the parameters that they have given me

If they don't want SPs is one thing but if they want to pull out data from the database in the form of reports there is no way you can bypass index creation. Beside that it doesn't make any sense because it will not affect the functionality but it WILL affect the performance when they will run these queries and it will be felt by the entire system.

.1 secs to run on the real database

SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,  
            MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
            MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
            MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
            MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
            MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
            CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
            FROM
            hours_log h  
            WHERE h.rYear = 2015 
            GROUP BY
            h.FIN_NUM,h.rmonth, h.ryear

Select allOpen in new window

Hours_log:
no indexes

That is your big problem because I believe that is the largest table in terms of records. That is why I asked you about that last query...

Yes that is correct and i agree with you

Is there a better way write the code that you would recommend

But on th eother hand you said it came up in 1 sec... that is not bad. You said in your question that you waited 30 secs. This would suggest that not the hours_log table is the problem but if it doesn't have indexes is a bit weird.

here are the results

with temp table
Affected rows: 6,084  Found rows: 6,084  Warnings: 0  Duration for 3 queries: 4.727 sec. (+ 27.643 sec. network)

without
Affected rows: 0  Found rows: 6,084  Warnings: 0  Duration for 1 query: 31.434 sec. (+ 2.325 sec. network)

You should try the "full" query multiple times not just once. Also run an EXPLAIN on it and see the results:

EXPLAIN
SELECT 
    GR.franchise_no, 
    GR.GROSSREV, 
    GR.WEEK1_HRS, 
    GR.WEEK2_HRS, 
    GR.WEEK3_HRS, 
    GR.WEEK4_HRS, 
    GR.WEEK5_HRS, 
    GR.TOTAL_HRS, 
    GR.ryear,
    GR.rmonth, 
    CASE 
	   WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
	   ELSE GR.GROSSREV 
    END FORECAST_GR
FROM 
(
    SELECT c.FIN_NUM, c.Franchise_no, ISNULL(R.grossrev,0.00) As grossrev, ISNULL(week1,0) WEEK1_HRS, ISNULL(week2,0) WEEK2_HRS, 
    ISNULL(week3,0) WEEK3_HRS, ISNULL(week4,0) WEEK4_HRS, ISNULL(week5,0) WEEK5_HRS, ISNULL(Total_hours,0) TOTAL_HRS, 
    c.ryear, c.rmonth
    FROM 
	 (SELECT * from franchise as a, 
	 (Select distinct ryear, rmonth from royalties) as b) as c     
    left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth

    LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,  
			 MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
			 MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
			 MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
			 MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
			 MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
			 CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
			 FROM
			 hours_log h  
			 WHERE h.rYear = 2015 
			 GROUP BY
			 h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear

)
 GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
		   FROM royalties RL 
           ) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by 
    GR.franchise_no, 
    GR.ryear asc, 
    GR.rmonth;

Select allOpen in new window

It is faster after running it about five time.  it takes about 3 secs.  huge improvement

My last part of the code for selecting the previous grossrev if the franchisee did not post anything.  how would I have it select the previous year and 12 (month) if the current month is 1

currently the code is

EFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
		   FROM royalties RL 
           ) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1

Select allOpen in new window

Which i know is incorrect

When you run a query like this for the first time is inconclusive, you need to run it few times to actually see the duration. As a general idea, when compare 2 different queries with the original one you will need to run them both same amount of times, 4-5 should be enough.

In regards to your question the best way to go is to use the actual datetime columns instead of Year,Month approach, from which you extract 12 months. There are functions for this:

SELECT (NOW(),INTERVAL -12 MONTH)

Let's clarify. What period you want to look back?

1. Current year
2. Last year
3. 12 months from current date

I would like to have be able to do a date range.

For example let's say we had a date range from 1/2013 to 10/2015 then the current query would be correct for all but 1/2015, 1/1014 and 1/2013 if a franchisee did not post anything for those months

The query will have to look at the month and if its 1 for the month  then it will have to look at the previous year and December.  For example 1/2015 will have to get 12/2014 Grossrev for its forecast

Thanks for the response:
I applied your supplied sql and created the current tables and the test data. This includes franchise (3 records), hours_log (24 records), and royalties (6 records).

List the required output.

I am not sure I understand why the ones from month 1 would not work if the rest do.,

the table doesn't have a datetime field.  it has only month and year both are int

I am still puzzled how the interval works and what are you looking for in an interval.

The interval is (1 = January, 2 = February, 3 = March, 4 = April, etc) and the four digit year ie 2015

I understood that. So you look at one moth at a time? I don't see that anywhere? Don't you look at an interval like past 6 or 12 months, or the last year data?

What I didn't understand is why is a problem for month=1?

Thank you for all of your help