MySQL Forums
Forum List  »  Partitioning

Partition Pruning Issue while using Day() function in Range
Posted by: Battu Praveen Kumar
Date: September 21, 2011 04:00AM

Using Mysql 5.1.50 Version, Understand how pruning works when the table is partitioned using Day function on date column type.

Case1: Partition InnoDB table using Day function, with indexes on sender and time

CREATE TABLE Messages_Day (
id varchar(100) DEFAULT NULL,
time datetime DEFAULT NULL,
sender varchar(100) DEFAULT NULL,
recipients text,
flist int(11) DEFAULT NULL,
subject text,
body text,
result int(11) DEFAULT NULL,
KEY INDEX_SENDER (sender),
KEY IDX_EVENT_TIME (time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( DAY(time))
(PARTITION P_Day_1 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION P_Day_2 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION P_Day_3 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION P_Day_4 VALUES LESS THAN (4) ENGINE = InnoDB,
....
PARTITION P_Day_30 VALUES LESS THAN (30) ENGINE = InnoDB,
PARTITION P_Day_31 VALUES LESS THAN (31) ENGINE = InnoDB
);

Populated the table with some test data…

PARTITION_NAME TABLE_ROWS *
P_Day_1 4235227

P_Day_13 4235227
P_Day_14 4235350
P_Day_15 4235350
P_Day_16 4235350

P_Day_27 4235350
P_Day_28 4235350
P_Day_29 4235350
P_Day_30 353101

Now when I run following query “explain partitions select * from messages_day where (time >= '2011-09-12' and time < '2011-09-13') and sender = '918648887399';”

id * select_type * table partitions type possible_keys key key_len ref rows Extra *
1 SIMPLE Messages_day P_Day_1,P_Day_2,P_Day_3,P_Day_4,P_Day_5,P_Day_6,P_Day_7,P_Day_8,P_Day_9,P_Day_10,P_Day_11,P_Day_12,P_Day_13,P_Day_14,P_Day_15,P_Day_16,P_Day_17,P_Day_18, P_Day_19,P_Day_20,P_Day_21,P_Day_22,P_Day_23,P_Day_24,P_Day_25,P_Day_26,P_Day_27,P_Day_28,P_Day_29,P_Day_30,P_Day_31 ref INDEX_SENDER,IDX_EVENT_TIME INDEX_SENDER 303 const 31 Using where

Questions:
1. Why select query looking into all the partitions? (Am I not executing correct sql?)
2. Why are Indexes not used?


Case 2: When the above table is Partitioned InnoDB table using To_days function, with indexes on sender and time

PARTITION BY RANGE ( TO_DAYS(`event_time`) )
(
PARTITION P_Day_01 VALUES LESS THAN (TO_DAYS('2011-09-01')),
PARTITION P_Day_02 VALUES LESS THAN (TO_DAYS('2011-09-02')),
.....
PARTITION P_Day_29 VALUES LESS THAN (TO_DAYS('2011-09-29')),
PARTITION P_Day_30 VALUES LESS THAN (TO_DAYS('2011-09-30'))

And when the above explain query is executed following result is uptained
+----+-------------+----------+----------------------------------+------+---------------------------+------------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+----------------------------------+------+---------------------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | messages_day | P_Day_01,P_Day_13,P_Day_14 | ref | IDX_EVENT_TIME,IDX_SENDER | IDX_SENDER | 768 | const | 31 | Using where |
+----+-------------+----------+----------------------------------+------+---------------------------+------------+---------+-------+------+-------------+

Questions:
1. Why select query looking into partitions 14?
2. As well why is it looking into partition 1? (may be null values are stored in partition 1, if yes how to avoid searching in partition 1).
2. Why are Indexes still not used?

How pruning works for a partition table using Day and To_Days function ?


Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition Pruning Issue while using Day() function in Range
4427
September 21, 2011 04:00AM


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.