Bug #30346 Support INTERVAL Partition Type

• Submitted: 9 Aug 2007 23:03
• Modified: 4 Jun 2010 13:43
• Reporter: Andrew A
• Status: Won't fix
• Category: MySQL Server: Partitions
• Severity: S4 (Feature request)
• OS: Any
• Assigned to: Mattias Jonsson
• CPU Architecture: Any
• Tags: interval, partition, partitioning

[9 Aug 2007 23:03] Andrew A

Description:
MySQL 5.1 suports partitioning with several types: http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

INTERVAL partitioning is an extension to these types which allows the database server to automatically create partitions as needed.

This removes the burden for application and end-user logic to create the required partitions for the database server.

INTERVAL partitioning works with RANGE, where an interval of for example "3 days" over a given DATETIME column will mean that when a new row is inserted with a DATETIME outside 3 days of the highest value, a new partition is added by the database server to handle that database row.

Quote from an Oracle database manual:
"Interval Partitioning:A new partitioning strategy in Oracle Database 11g,
Interval partitioning extends the capabilities of the range method to define equi-partitioned ranges using an interval definition.Rather than specifying individual ranges explicitly, Oracle will create any partition automatically as-needed whenever data for a partition is inserted for the very first time. 

Interval partitioning greatly improves the manageability of a partitioned table. For example, an interval partitionedtable could be defined so that Oracle creates a new partition for every month in a calendar year; a partition is then automatically created for 'September 2007' as soon as the first record for this month is inserted into the database.

The available techniques for an interval partitioned table are Interval, Interval- List, Interval-Hash,and Interval-Range."
Source: http://www.oracle.com/technology/products/bi/db/11g/pdf/partitioning-11g-whitepaper.pdf

This would be very much appreciated in the data warehouse environment, where time series data is added often to the database, and automating the addition of required partitions would be excellent.

How to repeat:
n/a

[9 Aug 2007 23:13] MySQL Verification Team

Thank you for the bug report feature request.

[27 Aug 2008 8:54] Mikael Ronström

No current plans for this feature

[12 Jan 2009 11:32] Mattias Jonsson

Marked bug#40372 as a duplicate of this.

[4 Jun 2010 13:43] Andrew A

I thought I'd give this a bump :) 

There have been some great new partitioning changes with each new release.

Is it possible to re-consider the 'interval' keyword for RANGE partitioning?

It would /dramatically/ simplify client software by allowing the database to automatically create these partitions instead of having to do it manually.

It would allow us to do something like:

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005)
    ) INTERVAL 5;

The simple addition of 'INTERVAL 5' (or for example, INTERVAL TO_DAYS(30)) would mean a new partition is made for every 5 year's after the last partition etc.

It would be a welcomed and awesome feature if there's room for it to be implemented.

Thanks