MySQL Forums
Forum List  »  Partitioning

Query to a partitioned table using all partitions
Posted by: Raghupradeep Aravindakshan
Date: November 06, 2016 11:22PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Query to a partitioned table using all partitions
605
November 06, 2016 11:22PM


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.