0

How can I speed up this 2m5s query that has indices?

select urls.id as urlId, 
    count(case when s1.hit_type = 0 then 1 end) as aCount, 
    count(case when s1.hit_type = 1 then 1 end) as bCount, 
    count(case when s1.hit_type = 2 then 1 end) as cCount, 
    count(distinct s1.source_id) as sourcesCount 
from urls join stats s1 on urls.id = s1.url_id 
where s1.hit_date >= '2017-12-12' 
group by urls.id 
order by aCount desc 
limit 0,100;

mysql> show create table stats;

| stats | CREATE TABLE `stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `url_id` varchar(100) DEFAULT NULL,
  `hit_date` datetime DEFAULT NULL,
  `hit_type` tinyint(4) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url_id_idx` (`url_id`),
  KEY `source_id` (`source_id`),
  KEY `stats_hit_date_idx` (`hit_date`),
  CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`),
  CONSTRAINT `stats_ibfk_2` FOREIGN KEY (`source_id`) REFERENCES `sources` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6027557 DEFAULT CHARSET=latin1 |

mysql> describe select...
| id | select_type | table   | type   | possible_keys                                                                                   | key     | key_len | ref                      | rows    | Extra                                        |
+----+-------------+---------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | s1      | ALL    | url_id_idx,stats_hit_date_idx                                                                   | NULL    | NULL    | NULL                     | 5869695 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | urls    | eq_ref | PRIMARY,urls_email_idx,urls_status_idx,deptId_idx,deptId_status_email_idx                       | PRIMARY | 102     | db.s1.url_id             |     1   | Using index                                  |

It doesn't seem to be using the hit_date index or url_id index.

I tried using a sub-select (select count(*) from stats where url_id = ... and hit_date >= ... and hit_type = 0) as aCount and it was faster and took 24s. Is there a way to make it less than 5s? The limit for the entire request is 30s.

MySQL Server version: 5.6.35-log MySQL Community Server (GPL)

  • Is hit_date >= '2017-12-12' the majority of the table? Test an compound index (hit_date, url_id). Can url_id be made shorter? – danblack Dec 12 '18 at 23:02
  • hit_date value will be by default 1 year ago. It could be any value from 1 year ago until today. I will try a compound index, with first 10 chars of url_id. – Chloe Dec 12 '18 at 23:07
  • 1
    The pattern count(case when s1.hit_type = 0 then 1 end) could be simplified to count(s1.hit_type = 0) however will be no performance difference. – danblack Dec 12 '18 at 23:38
  • 1
    @danblack I was playing with that before and actually it is sum(s1.hit_type = 0) because count would add up both 0 and 1 but sum will only add up 1's. select hit_type=0 returns 0 on false and 1 on true. – Chloe Dec 12 '18 at 23:58
  • 1
    @danblack - not count..., but SUM(s1.hit_type = 0) – Rick James Dec 13 '18 at 17:05
3

Your query is equal to

select /* urls.id */ s1.url_id as urlId, 
    count(case when s1.hit_type = 0 then 1 end) as aCount, 
    count(case when s1.hit_type = 1 then 1 end) as bCount, 
    count(case when s1.hit_type = 2 then 1 end) as cCount, 
    count(distinct s1.source_id) as sourcesCount 
from /* urls join */ stats s1 /* on urls.id = s1.url_id */
where s1.hit_date >= '2017-12-12' 
group by /* urls.id */ s1.url_id
order by aCount desc 
limit 0,100;

except in your query output there are only records which "pairs" exists in urls table.

But the constraint

CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`)

do not allow those records.

So my query is absolutely equal to your one, and you can use it instead.

To increase this query speed you may create covering index

ALTER TABLE stats ADD INDEX idx (url_id, hit_date, hit_type, source_id)

And the best way is to move url_id to a separate table and replace it with a reference of numeric type (grouping by VARCHAR field is expensive).


Additionally - count(case when s1.hit_type = N then 1 end) can be replaced with short SUM(s1.hit_type = N).


To speed up the whole query I'd recommend try to divide it to 4 separate queries:

SELECT urlId, 
       MAX(aCount) aCount, 
       MAX(bCount) bCount, 
       MAX(cCount) cCount, 
       MAX(sourcesCount) sourcesCount 
FROM (  select  s1.url_id as urlId, 
                COUNT(*) as aCount, 
                0 as bCount, 
                0 as cCount, 
                0 as sourcesCount 
        from stats s1 
        where s1.hit_date >= '2017-12-12' AND s1.hit_type = 0
        group by s1.url_id
      UNION ALL
        select  s1.url_id, 0, COUNT(*), 0, 0
        from stats s1 
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 1
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, COUNT(*), 0
        from stats s1 
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 2
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, 0, count(distinct s1.source_id)
        from stats s1 
        where s1.hit_date >= '2017-12-12' 
        group by s1.url_id
    ) x
GROUP BY urlId
order by aCount desc 
limit 0,100;

The index by (url_id, hit_type, hit_date) will speed up first 3 subqueries, and by (url_id, hit_date, source_id) will speed up the last subquery.

  • You are right. I am actually only selecting from one table. It reduced the time to 8.4s! I'll take it. The index did not help. The sum() expression did not help. The multi-query + UNION is genius! But alas, it took 25s instead. Even with the last 2 indices. Each sub-query takes about 6s. – Chloe Dec 13 '18 at 21:37
  • @Chloe But alas That didn't surprise me. The solution optimal in one case can be absolutely unapplicable in another case. For this reason, I have considered several different possibilities. Only practical tests can help in select the best solution. – Akina Dec 14 '18 at 4:55
1

Your query depends on wanting to get a summary after reading 5869695+ results and matching these in another table.

Getting this in < 5 seconds is a large ask.

As it seems your data is fairly constant after entry I'd suggest creating summary tables based on date and having a {a,b,c}Count.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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