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;
(PRIMARY KEY (id,hit_date))
on the subquery for stats table. This is obvious and expected. 1) The index withhit_date
as a prefix needed (and the best way - create the calculated fielddate(hit_date) AS date_hit_date VIRTUAL
and the index(date_hit_date, sc)
). 2) Replacewhere hit_date > date_sub(now(), interval 1 day)
withWHERE DATE(hit_date) > CURDATE() - INTERVAL 1 DAY
. 3) RemoveORDER BY
from subquery - it is ignored anyway. 4) Rewrite from correlated subquery to JOIN.sc
need to be in the index? The query withsc
anddelayed_stats
table only takes 1s. It is the other query withstats
table that is causing the delay (8s by itself, infinite time combined). Same forwhere hit_date > date_sub()
- that is on delayed_stats table which only takes 1s to query and will eventually be run forinterval 30 day
s. What do you mean by #4? Rewrite as I did at the bottom?SHOW CREATE TABLE
for both tables. Also, note that partition pruning failed, possibly implying that partitioning hurt rather than helped performance.