· 7 years ago · Nov 21, 2018, 11:52 PM
1EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i INNER JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-15' AND '2018-11-15 23:59:59' GROUP BY the_date;
2+------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+
5| 1 | SIMPLE | i | range | layer_id,created | created | 8 | NULL | 249116 | Using index condition; Using temporary; Using filesort |
6| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | getback.i.layer_id | 1 | Using index |
7+------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+
8
9EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i INNER JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-15' AND '2018-11-15 23:59:59' GROUP BY the_date;
10+------+-------------+-------+-------+------------------+------------+---------+--------------+------+----------------------------------------------+
11| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
12+------+-------------+-------+-------+------------------+------------+---------+--------------+------+----------------------------------------------+
13| 1 | SIMPLE | l | index | PRIMARY | voucher_id | 4 | NULL | 652 | Using index; Using temporary; Using filesort |
14| 1 | SIMPLE | i | ref | layer_id,created | layer_id | 4 | getback.l.id | 545 | Using where |
15+------+-------------+-------+-------+------------------+------------+---------+--------------+------+----------------------------------------------+
16
17EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i STRAIGHT_JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-15' AND '2018-11-15 23:59:59' GROUP BY the_date;
18+------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+
19| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
20+------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+
21| 1 | SIMPLE | i | range | layer_id,created | created | 5 | NULL | 272230 | Using index condition; Using temporary; Using filesort |
22| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | getback.i.layer_id | 1 | Using index |
23+------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+
24
25CREATE TABLE IF NOT EXISTS `layer` (
26`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
27`shop_id` int(10) unsigned NOT NULL,
28`voucher_id` int(10) unsigned NOT NULL,
29`templ_id` int(10) unsigned NOT NULL COMMENT 'Mailtemplate ID',
30`doi_templ_id` int(10) unsigned NOT NULL,
31`key` char(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
32`status` enum('active','inactive','deleted') NOT NULL DEFAULT 'active',
33`created` datetime NOT NULL,
34`createdby` int(10) unsigned NOT NULL,
35`modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
36PRIMARY KEY (`id`),
37KEY `shop_id` (`shop_id`,`status`) USING BTREE,
38KEY `key` (`key`),
39KEY `voucher_id` (`voucher_id`),
40KEY `templ_id` (`templ_id`),
41KEY `doi_templ_id` (`doi_templ_id`)
42) ENGINE=InnoDB AUTO_INCREMENT=656 DEFAULT CHARSET=utf8;
43
44CREATE TABLE IF NOT EXISTS `layer_impression` (
45`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
46`layer_id` int(10) unsigned NOT NULL,
47`session_id` bigint(20) unsigned NOT NULL,
48`device` enum('desktop','mobile','tablet') NOT NULL DEFAULT 'desktop',
49`created` datetime NOT NULL,
50PRIMARY KEY (`id`),
51KEY `layer_id` (`layer_id`),
52KEY `session_id` (`session_id`),
53KEY `created` (`created`,`device`) USING BTREE,
54CONSTRAINT `layer_impression_ibfk_1` FOREIGN KEY (`layer_id`) REFERENCES `layer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
55) ENGINE=InnoDB AUTO_INCREMENT=34184266 DEFAULT CHARSET=utf8;
56
57show index from layer_impression;
58+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
59| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
60+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
61| layer_impression | 0 | PRIMARY | 1 | id | A | 34062000 | NULL | NULL | | BTREE | | |
62| layer_impression | 1 | layer_id | 1 | layer_id | A | 62499 | NULL | NULL | | BTREE | | |
63| layer_impression | 1 | session_id | 1 | session_id | A | 34062000 | NULL | NULL | | BTREE | | |
64| layer_impression | 1 | created | 1 | created | A | 34062000 | NULL | NULL | | BTREE | | |
65| layer_impression | 1 | created | 2 | device | A | 34062000 | NULL | NULL | | BTREE | | |
66+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
67
68show index from layer_impression;
69+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
70| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
71+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
72| layer_impression | 0 | PRIMARY | 1 | id | A | 34295989 | NULL | NULL | | BTREE | | |
73| layer_impression | 1 | layer_id | 1 | layer_id | A | 18 | NULL | NULL | | BTREE | | |
74| layer_impression | 1 | session_id | 1 | session_id | A | 34295989 | NULL | NULL | | BTREE | | |
75| layer_impression | 1 | created | 1 | created | A | 34295989 | NULL | NULL | | BTREE | | |
76| layer_impression | 1 | created | 2 | device | A | 34295989 | NULL | NULL | | BTREE | | |
77+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
78
79WHERE l.shop_id = 1 # or
80WHERE l.shop_id IN (1,2,3)
81
82# AWS
83EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i LEFT JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-10' AND '2018-11-15 23:59:59' AND l.shop_id IN (42,36,103,63) GROUP BY the_date;
84+------+-------------+-------+-------+------------------+----------+---------+--------------+------+-----------------------------------------------------------+
85| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
86+------+-------------+-------+-------+------------------+----------+---------+--------------+------+-----------------------------------------------------------+
87| 1 | SIMPLE | l | range | PRIMARY,shop_id | shop_id | 4 | NULL | 11 | Using where; Using index; Using temporary; Using filesort |
88| 1 | SIMPLE | i | ref | layer_id,created | layer_id | 4 | getback.l.id | 545 | Using where |
89+------+-------------+-------+-------+------------------+----------+---------+--------------+------+-----------------------------------------------------------+
90
91
92# Old server
93EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i LEFT JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-10' AND '2018-11-15 23:59:59' AND l.shop_id IN (42,36,103,63) GROUP BY the_date;
94+------+-------------+-------+--------+------------------+---------+---------+--------------------+---------+--------------------------------------------------------+
95| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
96+------+-------------+-------+--------+------------------+---------+---------+--------------------+---------+--------------------------------------------------------+
97| 1 | SIMPLE | i | range | layer_id,created | created | 8 | NULL | 1615766 | Using index condition; Using temporary; Using filesort |
98| 1 | SIMPLE | l | eq_ref | PRIMARY,shop_id | PRIMARY | 4 | getback.i.layer_id | 1 | Using where |
99+------+-------------+-------+--------+------------------+---------+---------+--------------------+---------+--------------------------------------------------------+
100
101layer_impression: INDEX(created, layer_id)
102layer_impression: INDEX(layer_id, created)