0

This query takes forever to run (30+m - infinity).

select date, 
       sc, 
       ( select count(fingerprint_id) 
         from stats 
         where hit_date >= t.date 
           and hit_date < date_add('2020-01-20', interval 1 day) 
           and hit_type = 0 
           and fingerprint_id is not null ) as total_fingerprint
from ( select date(hit_date) as date, 
              sum(sc) as sc 
       from delayed_stats  
       where hit_date > date_sub(now(), interval 1 day) 
       group by date(hit_date) 
       order by hit_date) t;

The individual queries take 1s and 8s to run, but combined it never finishes. I expected 8-9s. If I replace t.date with the static '2020-01-20' then it takes 8s. Just replacing one static date with t.date cause the query to 'hang'. The minimum query which replicates this hanging is

select date, 
       (select count(fingerprint_id) from stats where hit_date >= t.date and hit_date < date_add(t.date, interval 1 day) and hit_type = 0 and fingerprint_id is not null) as total_fingerprint
from (select '2020-01-01' as date union select '2020-01-02' as date) t;

This is the query explanation:

+----+--------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------------+---------+------+-----------+----------+--------------------------------------------------------+
| id | select_type        | table         | partitions                                                                                                                                | type  | possible_keys | key          | key_len | ref  | rows      | filtered | Extra                                                  |
+----+--------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------------+---------+------+-----------+----------+--------------------------------------------------------+
|  1 | PRIMARY            | <derived3>    | NULL                                                                                                                                       | ALL   | NULL          | NULL         | NULL    | NULL |      7496 |   100.00 | NULL                                                   |
|  3 | DERIVED            | delayed_stats | NULL                                                                                                                                       | range | hit_date_idx  | hit_date_idx | 5       | NULL |      7496 |   100.00 | Using index condition; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | stats         | p20180101,p20180201,p20180301,p20180401,p20180501,p20180601,p20180701,p20180801,p20180901,p20181001,p20181101,p20181201,p20190101,p20190201,p20190301,p20190401,p20190501,p20190601,p20190701,p20190801,p20190901,p20191001,p20191101,p20191201,p20200101,p20200201 | ALL   | NULL          | NULL         | NULL    | NULL | 316867000 |     1.00 | Using where                                            |
+----+--------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------------+---------+------+-----------+----------+--------------------------------------------------------+
3 rows in set, 2 warnings (0.11 sec)

It doesn't appear to be using the hit_date index (PRIMARY KEY (id,hit_date)) on the subquery for stats table. My ultimate goal is to combine these two queries (interval 30 day):

select date(hit_date), 
       sum(sc) 
from delayed_stats 
where hit_date > date_sub(now(), interval 30 day) 
group by date(hit_date) 
order by hit_date;

select date(hit_date), 
       count(fingerprint_id) 
from stats 
where hit_date > date_sub(now(), interval 30 day) 
  and hit_type = 0 
  and fingerprint_id is not null 
group by date(hit_date) 
order by hit_date; -- 2m21s

When I see the query plan for the the 2nd query on stats table, then it shows the possible_keys as PRIMARY,source_id,stats_bag_id_idx. I tried another way to combine them, with a join, but that took 15m to run, when it should only take 2m.

select t.date, 
       sc, 
       fingerprint_count 
from ( select date(hit_date) date, 
              sum(sc) as sc 
       from delayed_stats 
       where hit_date > date_sub(now(), interval 30 day) 
       group by date(hit_date) 
       order by hit_date ) t 
join ( select date(hit_date) date, 
              count(fingerprint_id) as fingerprint_count 
       from stats 
       where hit_date > date_sub(now(), interval 30 day) 
         and hit_type = 0 
         and fingerprint_id is not null 
       group by date(hit_date) 
       order by hit_date ) t2 on t.date = t2.date;
3
  • 1
    It doesn't appear to be using the hit_date index (PRIMARY KEY (id,hit_date)) on the subquery for stats table. This is obvious and expected. 1) The index with hit_date as a prefix needed (and the best way - create the calculated field date(hit_date) AS date_hit_date VIRTUAL and the index (date_hit_date, sc)). 2) Replace where hit_date > date_sub(now(), interval 1 day) with WHERE DATE(hit_date) > CURDATE() - INTERVAL 1 DAY. 3) Remove ORDER BY from subquery - it is ignored anyway. 4) Rewrite from correlated subquery to JOIN.
    – Akina
    Commented Jan 21, 2020 at 4:47
  • Why would sc need to be in the index? The query with sc and delayed_stats table only takes 1s. It is the other query with stats table that is causing the delay (8s by itself, infinite time combined). Same for where hit_date > date_sub() - that is on delayed_stats table which only takes 1s to query and will eventually be run for interval 30 days. What do you mean by #4? Rewrite as I did at the bottom?
    – Chloe
    Commented Jan 22, 2020 at 20:25
  • Please provide SHOW CREATE TABLE for both tables. Also, note that partition pruning failed, possibly implying that partitioning hurt rather than helped performance.
    – Rick James
    Commented Jan 26, 2020 at 20:12

1 Answer 1

0

I solved it by using the last join example and was able to string together 8 different queries using this structure:

select date, sc, fc, fc10s, fc30s, ...
from (select date(hit_date) as date, ...) t
join (select date(hit_date) as date, ...) t2 on t.date = t2.date
join (...) t3 on t.date = t3.date
...

It took about 20m to run, which is OK for a report and less than I thought it would take.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.