Skip to content

Using UNIX_TIMESTAMP as a partitioning function for MySQL 5.1.43

It is now possible to use the UNIX_TIMESTAMP() function in partitioning expressions using TIMESTAMP columns.
MySQL 5.1.43 can be downloaded from
http://dev.mysql.com/downloads/
For example, it now possible to create a partitioned table such as this one :

It is now possible to use the UNIX_TIMESTAMP() function in partitioning expressions using TIMESTAMP columns.

MySQL 5.1.43 can be downloaded from

http://dev.mysql.com/downloads/

For example, it now possible to create a partitioned table such as this one :

CREATE TABLE t (id INTEGER, ts TIMESTAMP, col1 VARCHAR(64), ... )
PARTITION BY RANGE ( UNIX_TIMESTAMP(ts) ) (
PARTITION p0 VALUES LESS THAN (631148400),
PARTITION p1 VALUES LESS THAN (946681200),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

All other expressions involving TIMESTAMP values are now rejected with an error when attempting to create a new partitioned table or to alter an existing partitioned table.

To go from the unix timestamp to a more readable value it is possible to use the FROM_UNIXTIME(ts) function :

mysql> SELECT FROM_UNIXTIME(631148400);
+--------------------------------------+
| FROM_UNIXTIME(631148400) |
+--------------------------------------+
| 1990-01-01 00:00:00
+--------------------------------------+

With MySQL 5.1.43 it is important to note that partitioning can only be done on an integer value column or through the following restricted set of functions that return integer :

ABS() MOD() CEILING() FLOOR() EXTRACT() DATEDIFF()
DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() TO_DAYS() WEEKDAY()
HOUR() MINUTE() SECOND() TIME_TO_SEC() MICROSECOND()
YEAR() YEARWEEK() QUARTER() MONTH()

To help Giuseppe Maxia has proposed some tricks that make partitioning by date in 5.1 a bit less painful :
A quick usability hack with partitioning

The partition helper - Improving usability with MySQL 5.1 partitioning

And to partition on a non integer column :
Partitioning with non integer values using triggers

And do not forget that in 5.1 :
All the columns used in the table's partitioning expression mus t be used in every unique key on the table.
This also includes the table's primary key, since it is by definition a unique key.
So in the above example with the UNIX_TIMESTAMP function the timestamp column must be part of the primary key and also part of all the unique keys existing on the table.
An important restriction also to remember is that the number of partitions is limited to 1024 (including sub partitions).

MySQL 5.5. partitioning offers more functionalities :
With RANGE and LIST partitioning it will be possible to partition by non-integer columns like dates or strings (using the new COLUMNS keyword).
MySQL 5.5 Partitioning with the he COLUMNS keyword also allow to use multiple columns to define a partition.
The new TRUNCATE PARTITION allow to empty a partition in a more efficient way than delete or drop partition + recreation of the partition.
The new function TO_SECONDS is available to partition on time intervals smaller than one day. It is taken into account by partition pruning.

To explain 5.5 partitioning new features there is the interesting article by Giuseppe Maxia :
A deep look at MySQL 5.5 partitioning enhancements

Leave a Reply

Your email address will not be published. Required fields are marked *