MySQL Forums
Forum List  »  Partitioning

Range partitioning on floor (decimal(14.4)) doesn't work :/
Posted by: Max Herman
Date: July 12, 2014 12:35PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Range partitioning on floor (decimal(14.4)) doesn't work :/
2896
July 12, 2014 12:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.