Partitions didn`t work for me
I made following table difinition:
CREATE TABLE `tbl_result_1107296256_2` (
`StartTime` datetime NOT NULL,
`EndTime` datetime NOT NULL,
`InsertTime` datetime DEFAULT NULL,
`ObjectNo` int(11) NOT NULL,
`GranulityPeriod` smallint(6) DEFAULT NULL,
`ResultReliablityFlag` smallint(6) DEFAULT NULL,
`Counter_1107296257` decimal(12,3) DEFAULT NULL,
`Counter_1107296260` decimal(12,3) DEFAULT NULL,
`Counter_1107296261` decimal(12,3) DEFAULT NULL,
`Counter_1107296262` decimal(12,3) DEFAULT NULL,
`Counter_1107296263` decimal(12,3) DEFAULT NULL,
`Counter_1107296264` decimal(12,3) DEFAULT NULL,
`Counter_1107296265` decimal(12,3) DEFAULT NULL,
`Counter_1107296266` decimal(12,3) DEFAULT NULL,
`Counter_1107296267` decimal(12,3) DEFAULT NULL,
`Counter_1107296268` decimal(12,3) DEFAULT NULL,
`Counter_1107296269` decimal(12,3) DEFAULT NULL,
`Counter_1107296270` decimal(12,3) DEFAULT NULL,
`Counter_1107296271` decimal(12,3) DEFAULT NULL,
`Counter_1107296272` decimal(12,3) DEFAULT NULL,
`Counter_1107296273` decimal(12,3) DEFAULT NULL,
`Counter_1107296276` decimal(12,3) DEFAULT NULL,
`Counter_1107296281` decimal(12,3) DEFAULT NULL,
`Counter_1107296282` decimal(12,3) DEFAULT NULL,
`Counter_1107296283` decimal(12,3) DEFAULT NULL,
`Counter_1107296284` decimal(12,3) DEFAULT NULL,
PRIMARY KEY (`StartTime`,`ObjectNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (MONTH(StartTime)) (PARTITION p0 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (9) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (12) ENGINE = InnoDB) */
so, it describes 6 partitions by month, taken from DATE field in my table.
This table content information for 5 month of work in my company. From June till this November. According to this structure every month will be sittuated in separate partition.
Problem is when i`m try to made EXPLAIN PARTITION SELECT with some date range, output shows that all of maded partitions was in use:
EXPLAIN PARTITIONS SELECT * FROM tbl_result_1107296256_2 where StartTime<'2007-07-01';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_result_1107296256_2
partitions: p0,p1,p2,p3,p4,p5
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 754
Extra: Using where