MySQL Bugs: #47176: PARTITION BY RANGE with TIMESTAMP is not pruning

Bug #47176	PARTITION BY RANGE with TIMESTAMP is not pruning
Submitted:	7 Sep 2009 14:57	Modified:	24 Oct 2012 11:12
Reporter:	Nuno Tavares	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	date, datetime, partition, range

Description:
I'm trying to partition by a DATETIME field so I can prune a big part of the dataset when querying by a range. I've read lots of [apparently old] blog posts and docs about the subject, which suggest the initial YEAR() or TO_DAYS() approach. None seem to work.

According to:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html

I could even do it with MONTH() (since 5.1.12) but that's indeed how I started, and it won't work either.

Note: the integers below are date ranges starting at pJan2009 = '2009-02-01 00:00:00'.

I've noticed setting an INDEX won't help either.

How to repeat:
mysql> CREATE TABLE `part_range_myi` (
  `Time_end` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `SessionId` varchar(80) NOT NULL
) ENGINE=MyISAM
PARTITION BY RANGE ( TO_DAYS(Time_end) )
(PARTITION pOlder2009 VALUES LESS THAN (733773) ENGINE = MyISAM,
 PARTITION pJan2009 VALUES LESS THAN (733804) ENGINE = MyISAM,
 PARTITION pFeb2009 VALUES LESS THAN (733832) ENGINE = MyISAM,
 PARTITION pMar2009 VALUES LESS THAN (733863) ENGINE = MyISAM,
 PARTITION pApr2009 VALUES LESS THAN (733893) ENGINE = MyISAM,
 PARTITION pMay2009 VALUES LESS THAN (733924) ENGINE = MyISAM,
 PARTITION pJun2009 VALUES LESS THAN (733954) ENGINE = MyISAM,
 PARTITION pJul2009 VALUES LESS THAN (733985) ENGINE = MyISAM,
 PARTITION pAug2009 VALUES LESS THAN (734016) ENGINE = MyISAM,
 PARTITION pSep2009 VALUES LESS THAN (734046) ENGINE = MyISAM,
 PARTITION pOct2009 VALUES LESS THAN (734077) ENGINE = MyISAM,
 PARTITION pNov2009 VALUES LESS THAN (734107) ENGINE = MyISAM,
 PARTITION pDec2009 VALUES LESS THAN (734138) ENGINE = MyISAM,
 PARTITION pCatchAll VALUES LESS THAN MAXVALUE ENGINE = MyISAM);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into part_range_myi select Time_end,SessionId from arq_internet_part_rang_sourcedata;
Query OK, 710619 rows affected (2.19 sec)
Records: 710619  Duplicates: 0  Warnings: 0

mysql>  explain partitions select * from part_range_myi where Time_end BETWEEN '2009-07-01 01:00:00' AND '2009-07-15 01:00:00';  
+----+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table          | partitions                                                                                                                       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | part_range_myi | pOlder2009,pJan2009,pFeb2009,pMar2009,pApr2009,pMay2009,pJun2009,pJul2009,pAug2009,pSep2009,pOct2009,pNov2009,pDec2009,pCatchAll | ALL  | NULL          | NULL | NULL    | NULL | 710619 | Using where | 
+----+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> select count(1) from part_range_myi  where Time_end BETWEEN '2009-07-01 01:00:00' AND '2009-07-15 01:00:00';  
+----------+
| count(1) |
+----------+
|   659913 | 
+----------+
1 row in set (1.18 sec)

However, equality causes pruning being done:

mysql>  explain partitions select * from part_range_myi where Time_end = '2009-07-01 01:00:00';                            
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | part_range_myi | pJul2009   | ALL  | NULL          | NULL | NULL    | NULL | 710619 | Using where | 
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Now with an index:
mysql> truncate table part_range_myi;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table part_range_myi add index idx_test(Time_end);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into part_range_myi select Time_end,SessionId from arq_internet_wifi_range_myi_orig;
Query OK, 710619 rows affected (4.03 sec)
Records: 710619  Duplicates: 0  Warnings: 0

mysql>  explain partitions select * from part_range_myi where Time_end BETWEEN '2009-03-01 01:00:00' AND '2009-03-02 01:00:00';  
+----+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table          | partitions                                                                                                                       | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | part_range_myi | pOlder2009,pJan2009,pFeb2009,pMar2009,pApr2009,pMay2009,pJun2009,pJul2009,pAug2009,pSep2009,pOct2009,pNov2009,pDec2009,pCatchAll | range | idx_test      | idx_test | 4       | NULL |   14 | Using where | 
+----+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

Suggested fix:
Prune by range, especially if it's a PARTITION BY RANGE schema.

Thanks for your bug report.
Verified as described, although the header was incorrect. It is only with TIMESTAMP that it doesn't prune. With DATETIME, it does fine.

A partitioned table using timestamps partitioned by range doesn't prune on range selects.

Simple test case follows:

# first, using DATETIME
create table t1 (dt datetime) 
partition by range (to_days(dt)) 
(partition p1 values less than (to_days('2009-01-01')), 
partition p2 values less than (to_days('2009-02-01')));

insert into t1 values ('2009-01-15 00:00:00'), ('2009-01-16 00:00:00');

explain partitions select * from t1 where dt between '2009-01-10 00:00:00' and '2009-01-20 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where

# next, using TIMESTAMPS
alter table t1 modify dt timestamp;
explain partitions select * from t1 where dt between '2009-01-10 00:00:00' and '2009-01-20 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p1,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where

Workaround: replace TIMESTAMP with DATETIME and use a trigger to eventually insert current times.

What about using MONTH() on DATETIME? Should it be filed on separate bug report? 

mysql> CREATE TABLE `t1` (
    ->   `dt` datetime DEFAULT NULL
    -> ) ENGINE=InnoDB 
    -> PARTITION BY RANGE (month(dt))
    -> (PARTITION pJan VALUES LESS THAN (2) ENGINE = InnoDB,
    ->  PARTITION pFeb VALUES LESS THAN (3) ENGINE = InnoDB, 
    ->  PARTITION pCatchAll VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values ('2009-01-15 00:00:00'), ('2009-01-16 00:00:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain partitions  select * from t1 where dt BETWEEN '2009-01-01 01:00:00' AND '2009-02-02 01:00:00'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: pJan,pFeb,pCatchAll
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

Thanks for pointing the correctness of subject, I was struggling with both (DATETIME and TIMESTAMP).

partition by range using MONTH() does not trigger the pruning. The manual says clearly that only YEAR and TO_DAYS are optimized for pruning. See my article on partitioning 
http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

TIMESTAMP is not supported for pruning in TO_DAYS() or YEAR(). Implementing the pruning support would be possible, but since the handling of TIMESTAMP internally in the mysql server is affected by the session variable time_zone, TIMESTAMP is not deterministic for use in partitioning function and will probably be disallowed, see bug#42849.

I disagree.

RANGE PARTITIONing on a TIMESTAMP can be DETERMINISTIC by defining that the value used for picking the PARTITION is the UTC equivalent of the TIMESTAMP given by the users.  After all, that is what is stored.

Furthermore, it can and should be treated as monotonic.

(OK, I don't have an answer for what to do about Leap seconds; just make a rule.)

Fixed in 5.5+ by bug#28928.