Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 |
Add Comment
Please, Sign In to add comment