Hello,
I have a table a history table which keeps data for last 1 month and is purged. This table have about 10 million records. I partitioned this table using range partition on a datetime column. The table and partition definition is as below.
CREATE TABLE messages_history (
HistoryId BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
EventID BIGINT(20) UNSIGNED NOT NULL,
SourceDevice VARCHAR(20) NOT NULL,
DestinationDevice VARCHAR(20) NOT NULL,
EventTime DATETIME NOT NULL,
Event VARCHAR(15) NOT NULL,
Message VARCHAR(255) NOT NULL,
PRIMARY KEY (HistoryId,EventTime),
KEY indx_messages_new_message (Message),
KEY idx_eventtime (EventTime)
) ENGINE=MYISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (DAY(EventTime))
(PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM,
PARTITION p31 VALUES LESS THAN (32) ENGINE = MyISAM) */
Now, when I am querying this table for records between two dates, the explain plan show that it is looking into all partitions even though I am searching for couple of days. Query and explain plan is below.
EXPLAIN PARTITIONS
SELECT * FROM messages_history WHERE eventTime >= ‘2016-10-24 00:00:00’ AND eventTime < ‘2016-10-26 00:00:00’;
“id” “select_type” “table” “partitions” “type” “possible_keys” “key” “key_len” “ref” “rows” “filtered” “Extra”
“1” “SIMPLE” “messages_history” “p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31” “ALL” “idx_eventtime” \N \N \N “2547217” “100.00” “Using where”
If I am querying like below its using partitions.
EXPLAIN PARTITIONS
SELECT * FROM messages_history WHERE eventTime = ‘2016-10-24 00:00:00’
“id” “select_type” “table” “partitions” “type” “possible_keys” “key” “key_len” “ref” “rows” “filtered” “Extra”
“1” “SIMPLE” “messages_history” “p24” “ref” “idx_eventtime” “idx_eventtime” “5” “const” “2” “100.00” \N
So, what is wrong in the first query ? Was it supposed to use just two partitions instead of looking for data in all. Am I doing anything wrong ? Please help me on this.
With Regards
Raghupradeep