Description:
CREATE TABLE `message_list` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Column id is a sequence number generated by database as primary key (PK) for each record',
`timeposted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Column timeposted is the posting time stamp of a message',
`complete` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column complete is a flag of the stage of message process: 0--not processed, 1--handled, 2--read, 3--failed',
`appid` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column appid is the interface appid',
`fromfld` varchar(61) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column fromfld is the FROM field of the message',
`fromhost` varchar(40) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column fromhost is the message coming from ',
`deviceid` int(11) DEFAULT NULL COMMENT 'Column deviceid is the id of a device that references back to id in device table and could be null',
`tofld` varchar(61) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column tofld is the TO field of the message',
`tohost` varchar(40) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column tohost is the message will be sent to ',
`subject` varchar(180) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column subject is the SUBJECT field of the message',
`body` blob COMMENT 'Column body is the BODY field of the message',
`retrycount` int(11) DEFAULT NULL COMMENT 'Column retrycount is the number of retries',
`timeretry` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Column timeretry is the time of last retry',
`sequence` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column sequence is the sequence number of the message from device',
`ackd` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column ackd is a flag to indicate the message has been acked',
`routeto` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column routeto is the type of route (see QMessageDBconst class)',
`type` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column type is the message type (see QMessageDBconst class)',
`timepostedex` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Column timepostedex is the time of the message posted',
`reason` int(11) DEFAULT '0' COMMENT 'Column reason is the reson of the message',
`reasonid` int(11) DEFAULT NULL COMMENT 'Column reasonid is reason id of the message which set and used by application',
`class` varchar(255) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column class is the call back class',
`dispseq` int(11) DEFAULT NULL COMMENT 'Column dispseq is default to 0',
`dispstate` varchar(30) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column dispstate is the message category',
`refid` varchar(30) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'Column refid is the reference id of the message',
`priority` int(11) DEFAULT NULL COMMENT 'Column priority is the priority level of the message 1-5 (low to high) default to 0',
`gpsrhid` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Column gpsrhid is AFK to gpsreadingshistory.id',
PRIMARY KEY (`id`,`timeposted`),
KEY `message_RR_I` (`reason`,`reasonid`),
KEY `message_RI_I` (`reasonid`),
KEY `message_SEQFLDIDX` (`sequence`),
KEY `message_DEVFLDIDX` (`deviceid`),
KEY `message_timepostedex` (`timepostedex`),
KEY `message_ffi_idx` (`fromhost`,`fromfld`,`id`),
KEY `message_fti_idx` (`fromhost`,`tofld`,`id`),
KEY `message_to_fldhost` (`tofld`,`tohost`),
KEY `message_trctr_idx` (`routeto`,`complete`,`tofld`,`tohost`,`retrycount`)
) ENGINE=InnoDB COMMENT='See schema_dictionary table for details'
PARTITION BY list (month(timeposted))
(
PARTITION p1 values in (1),
PARTITION p2 values in (2),
PARTITION p3 values in (3),
PARTITION p4 values in (4),
PARTITION p5 values in (5),
PARTITION p6 values in (6),
PARTITION p7 values in (7),
PARTITION p8 values in (8),
PARTITION p9 values in (9),
PARTITION p10 values in (10),
PARTITION p11 values in (11),
PARTITION p12 values in (12)
);
C:\PROJECTS\Mysql\wtwSql\DATABASE\patch>mysql -uroot -pmydevsql -hstang quadrant
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.1.42-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> explain partitions select count(*) from message_list where timeposted between '20090131' and '20090231'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: message_list
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12
type: index
possible_keys: NULL
key: message_timepostedex
key_len: 4
ref: NULL
rows: 12
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
My expected result of partitions should only be: p1, p2.
What is wrong with MySQL partitioning?
Please help.
Steven
How to repeat:
run explain statement and you always get the same result.
Description: CREATE TABLE `message_list` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Column id is a sequence number generated by database as primary key (PK) for each record', `timeposted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Column timeposted is the posting time stamp of a message', `complete` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column complete is a flag of the stage of message process: 0--not processed, 1--handled, 2--read, 3--failed', `appid` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column appid is the interface appid', `fromfld` varchar(61) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column fromfld is the FROM field of the message', `fromhost` varchar(40) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column fromhost is the message coming from ', `deviceid` int(11) DEFAULT NULL COMMENT 'Column deviceid is the id of a device that references back to id in device table and could be null', `tofld` varchar(61) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column tofld is the TO field of the message', `tohost` varchar(40) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column tohost is the message will be sent to ', `subject` varchar(180) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column subject is the SUBJECT field of the message', `body` blob COMMENT 'Column body is the BODY field of the message', `retrycount` int(11) DEFAULT NULL COMMENT 'Column retrycount is the number of retries', `timeretry` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Column timeretry is the time of last retry', `sequence` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column sequence is the sequence number of the message from device', `ackd` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column ackd is a flag to indicate the message has been acked', `routeto` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column routeto is the type of route (see QMessageDBconst class)', `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Column type is the message type (see QMessageDBconst class)', `timepostedex` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Column timepostedex is the time of the message posted', `reason` int(11) DEFAULT '0' COMMENT 'Column reason is the reson of the message', `reasonid` int(11) DEFAULT NULL COMMENT 'Column reasonid is reason id of the message which set and used by application', `class` varchar(255) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column class is the call back class', `dispseq` int(11) DEFAULT NULL COMMENT 'Column dispseq is default to 0', `dispstate` varchar(30) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column dispstate is the message category', `refid` varchar(30) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'Column refid is the reference id of the message', `priority` int(11) DEFAULT NULL COMMENT 'Column priority is the priority level of the message 1-5 (low to high) default to 0', `gpsrhid` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Column gpsrhid is AFK to gpsreadingshistory.id', PRIMARY KEY (`id`,`timeposted`), KEY `message_RR_I` (`reason`,`reasonid`), KEY `message_RI_I` (`reasonid`), KEY `message_SEQFLDIDX` (`sequence`), KEY `message_DEVFLDIDX` (`deviceid`), KEY `message_timepostedex` (`timepostedex`), KEY `message_ffi_idx` (`fromhost`,`fromfld`,`id`), KEY `message_fti_idx` (`fromhost`,`tofld`,`id`), KEY `message_to_fldhost` (`tofld`,`tohost`), KEY `message_trctr_idx` (`routeto`,`complete`,`tofld`,`tohost`,`retrycount`) ) ENGINE=InnoDB COMMENT='See schema_dictionary table for details' PARTITION BY list (month(timeposted)) ( PARTITION p1 values in (1), PARTITION p2 values in (2), PARTITION p3 values in (3), PARTITION p4 values in (4), PARTITION p5 values in (5), PARTITION p6 values in (6), PARTITION p7 values in (7), PARTITION p8 values in (8), PARTITION p9 values in (9), PARTITION p10 values in (10), PARTITION p11 values in (11), PARTITION p12 values in (12) ); C:\PROJECTS\Mysql\wtwSql\DATABASE\patch>mysql -uroot -pmydevsql -hstang quadrant Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.1.42-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> explain partitions select count(*) from message_list where timeposted between '20090131' and '20090231'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: message_list partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 type: index possible_keys: NULL key: message_timepostedex key_len: 4 ref: NULL rows: 12 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) My expected result of partitions should only be: p1, p2. What is wrong with MySQL partitioning? Please help. Steven How to repeat: run explain statement and you always get the same result.