Range partitioning on floor (decimal(14.4)) doesn't work :/
Hello,
I have large table, and I added partitions by range to it:
CREATE TABLE `events` (
`type` tinyint(3) unsigned NOT NULL DEFAULT '0',
`time` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
`data` float DEFAULT NULL,
`message` text CHARACTER SET utf8,
`sensor_id` int(11) NOT NULL,
KEY `speed` (`sensor_id`,`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (FLOOR(`time`))
(PARTITION p2014_01 VALUES LESS THAN (1391230800) ENGINE = MyISAM,
PARTITION p2014_02 VALUES LESS THAN (1393650000) ENGINE = MyISAM,
PARTITION p2014_03 VALUES LESS THAN (1396324800) ENGINE = MyISAM,
PARTITION p2014_04 VALUES LESS THAN (1398916800) ENGINE = MyISAM,
PARTITION p2014_05 VALUES LESS THAN (1401595200) ENGINE = MyISAM,
PARTITION p2014_06 VALUES LESS THAN (1404187200) ENGINE = MyISAM,
PARTITION p2014_07 VALUES LESS THAN (1406865600) ENGINE = MyISAM,
PARTITION p2014_08 VALUES LESS THAN (1409544000) ENGINE = MyISAM,
PARTITION p2014_09 VALUES LESS THAN (1412136000) ENGINE = MyISAM,
PARTITION p2014_10 VALUES LESS THAN (1414814400) ENGINE = MyISAM,
PARTITION p2014_11 VALUES LESS THAN (1417410000) ENGINE = MyISAM,
PARTITION p2014_12 VALUES LESS THAN (1420088400) ENGINE = MyISAM,
PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
time decimal(14,4) - is linux time-stamp with milliseconds.
So it's basically a partitioning by month.
However when I explain queries, mysql says that it wants to use all partitions:
EXPLAIN PARTITIONS select * from events where time<1388552400;
| 1 | SIMPLE | events | p2010,p2011,p2012,p2013,p2014_01,p2014_02,p2014_03,p2014_04,p2014_05,p2014_06,p2014_07,p2014_08,p2014_09,p2014_10,p2014_11,p2014_12,p2015_01,p2015_02,p2015_03,p2015_04,p2015_05,p2015_06,p2015_07,p2015_08,p2015_09,p2015_10,p2015_11,p2015_12,p_max | ALL | NULL | NULL | NULL | NULL | 100164100 | Using where |
mysql> EXPLAIN PARTITIONS select * from events where sensor_id=1320 and time<1388552400;
| 1 | SIMPLE | events | p2010,p2011,p2012,p2013,p2014_01,p2014_02,p2014_03,p2014_04,p2014_05,p2014_06,p2014_07,p2014_08,p2014_09,p2014_10,p2014_11,p2014_12,p2015_01,p2015_02,p2015_03,p2015_04,p2015_05,p2015_06,p2015_07,p2015_08,p2015_09,p2015_10,p2015_11,p2015_12,p_max | range | speed | speed | 11 | NULL | 1552948 | Using where |
Any suggestions?
Thanks!