|
RFC 3339 (RFC3339 time) times are not handled consistently, and specifically when it comes to partition pruning.
For reference, more on RFC 3339 can be found here:
https://www.ietf.org/rfc/rfc3339.txt
A normal datetime looks like:
2016-12-12 11:10:09
A RFC 3339 time might look like:
2016-12-14T11:09:09-05:00
The main problem I see right now is with partition pruning, but perhaps it applies to other areas.
Test case:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE `t1` (
|
`id` int,
|
`GMTDataTime` datetime NOT NULL,
|
PRIMARY KEY (`GMTDataTime`,`id`),
|
KEY `id` (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
PARTITION BY LIST ( TO_DAYS(GMTDataTime))
|
(PARTITION p1 VALUES IN (736673),
|
PARTITION p2 VALUES IN (736674),
|
PARTITION p3 VALUES IN (736675));
|
INSERT INTO `t1` VALUES (1,'2016-12-10 10:10:10');
|
INSERT INTO `t1` VALUES (2,'2016-12-11 11:11:11');
|
INSERT INTO `t1` VALUES (3,'2016-12-12 12:12:12');
|
SELECT * FROM `t1`;
|
EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10 10:10:10' AND '2016-12-11 11:11:11';
|
EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10T10:10:10-05:00' AND '2016-12-11T11:11:11-05:00';
|
Note how the latter, using RFC 3339 time, does not use partition pruning (it should have excluded "p3" in "partitions" column):
mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10 10:10:10' AND '2016-12-11 11:11:11';
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | p1,p2 | index | PRIMARY | PRIMARY | 9 | NULL | 2 | Using where; Using index |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10T10:10:10-05:00' AND '2016-12-11T11:11:11-05:00';
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | p1,p2,p3 | index | PRIMARY | PRIMARY | 9 | NULL | 3 | Using where; Using index |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
There are a couple of hacky work-arounds where you essentially re-convert the RFC 3339 time to a 'typical' datetime, which back allows partition pruning to take place:
mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN DATE_FORMAT('2016-12-10T10:10:10-05:00','%Y-%m-%d %H:%i:%s') AND DATE_FORMAT('2016-12-11T11:11:11-05:00','%Y-%m-%d %H:%i:%s');
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | p1,p2 | index | PRIMARY | PRIMARY | 9 | NULL | 2 | Using where; Using index |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set, 6 warnings (0.00 sec)
|
|
mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN FROM_UNIXTIME(UNIX_TIMESTAMP('2016-12-10T10:10:10-05:00')) AND FROM_UNIXTIME(UNIX_TIMESTAMP('2016-12-11T11:11:11-05:00'));
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | p1,p2 | index | PRIMARY | PRIMARY | 9 | NULL | 2 | Using where; Using index |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set, 6 warnings (0.00 sec)
|
But this is quite hacky, not always possible for queries on-the-fly, and still returns [many] warnings.
|