Incredibly Slow Query

Question

Last week I asked a question and incorporated https://stackoverflow.com/a/39149076/6489232.

Below I have put the full query, which does work. But it is incredibly slow (2 minutes for a single date range on the largest database, 20 seconds on the smallest), which is to be expected when doing it the awkward way I have, with a triple select.

Select Sources,
Description,
Start,
Series,
Circulation,
Advert,
COG,
COF,
Orders,
ResponseP,
OrderValue,
AOV,
Marketing,
Ins,
New,
truncate((OrderValue + Ins) - (COG + COF + Marketing + Advert),2) as Profit,
truncate(((OrderValue + Ins) - (COG + COF + Marketing + Advert))/New,2) as CPN 
from
(select Sources as Sources,
 Description as Description,
 Start as Start,
 Series as Series,
 Circulation as Circulation,
 Advert as Advert,
 sum(i.itmcos) as COG,
 truncate(sum(k.stkffc)*i.itmmul,2) as COF,
 max(Orders) as Orders,
 ResponseP as ResponseP,
 max(OrderValue) as OrderValue,
 AOV as AOV,
Marketing as Marketing, 
Ins as Ins,
 New as New
  from (
        select s.soucod as Sources,
      s.soudes as Description,
      o.ordurn as ord,
      s.souexp as Ins,
      s.soudts as Start,
      s.soucir as Circulation,
      s.souser as Series,
      s.souavc as Advert, 
      count(o.ordurn) as Orders,
      sum(o.ordval) as OrderValue,
      truncate((count(o.ordurn)/s.soucir)*100,2) as ResponseP,
      truncate(sum(o.ordval)/count(o.ordurn),2) as AOV,
      truncate(sum(CASE WHEN o.ordpot = 'F' THEN o.ordval ELSE 0 END)*0.32,2) as Marketing,
      sum(CASE WHEN o.CCSCRD = m.CCSCRD then 1 else 0 end) as New
          from source s
          join orders o on o.ordsou = s.soucod
          join member m on o.ORDMEM = m.MEMKEY
      where o.ordrdt = '2016-08-30'
         group by s.soucod
       ) A
   join items i on i.ITmsou = A.Sources
   join stock k on i.itmstk = k.stkcod
 where i.itmrdt = '2016-08-30'
  group by A.Sources) B
  group by B.Sources

I had to add the third "Select" because I couldnt put the fields I defined in the second Select into a calculation in that same select. Below is what I am looking to do to possibly speed this up.

select Sources,
 Description,
 Start,
 Series,
 Circulation,
 Advert,
 sum(i.itmcos) as COG,
 truncate(sum(k.stkffc)*i.itmmul,2) as COF,
 max(Orders) as Orders,
 ResponseP,
 max(OrderValue) as OrderValue,
 AOV,
Marketing, 
Ins,
 New,
 truncate((OrderValue + Ins) - (COG + COF + Marketing + Advert),2) as Profit,
truncate(((OrderValue + Ins) - (COG + COF + Marketing + Advert))/New,2) as CPN 
  from (
        select s.soucod as Sources,
      s.soudes as Description,
      o.ordurn as ord,
      s.souexp as Ins,
      s.soudts as Start,
      s.soucir as Circulation,
      s.souser as Series,
      s.souavc as Advert, 
      count(o.ordurn) as Orders,
      sum(o.ordval) as OrderValue,
      truncate((count(o.ordurn)/s.soucir)*100,2) as ResponseP,
      truncate(sum(o.ordval)/count(o.ordurn),2) as AOV,
      truncate(sum(CASE WHEN o.ordpot = 'F' THEN o.ordval ELSE 0 END)*0.32,2) as Marketing,
      sum(CASE WHEN o.CCSCRD = m.CCSCRD then 1 else 0 end) as New
          from source s
          join orders o on o.ordsou = s.soucod
          join member m on o.ORDMEM = m.MEMKEY
      where o.ordrdt = '2016-08-30'
         group by s.soucod
       ) A
   join items i on i.ITmsou = A.Sources
   join stock k on i.itmstk = k.stkcod
 where i.itmrdt = '2016-08-30'
  group by A.Sources

But this fails due to unknown field, obviously the COF, COG etc fields that were created in that select. So I added the third select to do this a stage higher. Is there a way of doing this without the third select being needed?

Answer 1

I can suggest - in a first step - to create a SQL "view":

CREATE VIEW view_name AS
select s.soucod as Sources,
s.soudes as Descrption,
o.ordurn as ord, (etc...),
o.ordrdt -- add this column intending to use it in where clause later
FROM source s JOIN orders o ON o.ordsou = s.soucod
JOIN member m ON o.ORDMEM = m.MEMKEY
GROUP BY s.soucod;

You can look at this link to know how SQL views can raise performances: MySql views performance

That supposes that all your database must be properly indexed.

Then, your final query could look like this:

SELECT sources, description (etc...)
FROM view_name A
JOIN items i ON i.ITmsou = A.Sources
JOIN stock k ON i.itmstk = k.stkcod
WHERE i.itmrdt = '2016-08-30' AND A.ordrdt = i.itmrdt
GROUP BY A.Sources;

That will execute a "GROUP BY" just one time in that query. The first "GROUP BY" is executed beforehand by the view. Many GROUP BY with "COUNT, SUM..." and all calculated statements into a single query are often slow.