CODEWITHSUNDEEP
mysqldatecount
sergi castells
sergi castells

Reputation: 3

Speed up query table temporaly

I need to speed up a query in mysql that takes 45 seconds, I need to count for months / years including the months that there are no results in the table, for that I create a temporary table of dates and then I launch the following query, how can I speed it up?

//CREATE TABLE OF DATES AND INSERT
CREATE TEMPORARY  TABLE fechas(fecha date);

INSERT INTO fechas(fecha)
        select * from 
        (select adddate("1970-01-01",t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    where selected_date between "2017-01-01" and "2018-01-01" 
    order by selected_date;

//QUERY

SELECT DATE_FORMAT(f.fecha, "%m-%Y") as data, 
        ifnull((
            SELECT ifnull(COUNT(*),0) 
            FROM cupons c 
            WHERE YEAR(c.dataCupo) = YEAR(f.fecha) 
            AND MONTH(c.dataCupo) = MONTH(f.fecha)  
            and c.empresa=1 
            AND c.proveidor!="VINCULADO"
            group by YEAR(c.dataCupo), MONTH(c.dataCupo)
            ORDER BY c.dataCupo
        ),0)  AS num 
FROM fechas f 
where f.fecha BETWEEN "2017-01-01" and "2018-01-01" 
GROUP BY YEAR(f.fecha),MONTH(f.fecha);

//Result:

data | num

01-2018 | 15

02-2018 | 0

03-2018 | 20

04-2018 | 0

....

Upvotes: 0

Views: 46

Answers (1)

spencer7593
spencer7593

Reputation: 108480

Wrapping columns in functions is going to prevent MySQL from making effective use of an index.

WHERE YEAR(c.dataCupo) = ... 
  AND MONTH(c.dataCupo) = ...

Here's a pattern that allows MySQL to use a range scan operation on an appropriate index 

WHERE c.dataCupo >=  ... 
  AND c.dataCupo <   ...

The correlated subquery in the SELECT list is going to get executed multiple times. If we are going to go that route, then it would be much better to get the grouping of fechas done FIRST, and then execute the subquery for each month, cuts down on the number of executions.

But I wouldn't do that, I would use an outer join operation.

And I would get only get one row per month from fechas, and get the corresponding counts by month, rather than getting them by day.

With no change to the temporary table, then something like this:

SELECT DATE_FORMAT(f.fecha, "%m-%Y")  AS data
     , COUNT(c.dataCupo)              AS num
  FROM fechas f
  LEFT
  JOIN cupons c 
    ON c.dataCupo  >= f.fecha
   AND c.dataCuop   < f.fecha + INTERVAL 1 MONTH
   AND c.empresa    = 1
   AND c.proveidor != 'VINCULADO'
 WHERE f.fecha >= '2017-01-01'
   AND f.fecha  < '2018-01-01'
   AND DATE_FORMAT(f.fecha,'%d') = '01'
 GROUP 
    BY f.fecha

Note the condition DATE_FORMAT(f.fecha,'%d') = '01' is getting us just the first day of the month. It looks like populating the temporary table gets us unique date values, so we won't get duplicates.

Upvotes: 1

Related Questions