MySQL Forums
Forum List  »  Partitioning

Re: HOW TO: PARTITION USING "TIMESTAMP"
Posted by: Jonathan Stephens
Date: March 20, 2007 07:10PM

Hi,

Ijaz wanted to partition on a TIMESTAMP column. The point I was trying to make in my response to him was that - all other things being equal - if you need the current date/time stored automatically when you insert a row, then DATETIME plus an INSERT trigger does that job just as well as TIMESTAMP.

While you can't partition directly on a DATETIME column, you can use one of MySQL's date/time functions to provide an integer value in a partitioning expression, as discussed here: http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

More specifically, you can take advantage of partition pruning (see http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html) when using one of the functions YEAR() or TO_DAYS() on a DATE or DATETIME column.

You cannot use either of these functions (or any other date/time functions) on a TIMESTAMP column.

Storage space is also a valid consideration. If you really need to save that 4 bytes per row, then use an INT column and set an INSERT trigger on it, like this:

mysql> CREATE TABLE test.m (val INT, ts INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER r BEFORE INSERT ON test.m FOR EACH ROW SET new.ts = UNIX_TIMESTAMP();
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO m VALUES (12345, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM m;
+-------+------------+
| val   | ts         |
+-------+------------+
| 12345 | 1174438267 |
+-------+------------+
1 row in set (0.00 sec)

If you need to display the ts column as a datetime:

mysql> SELECT val,FROM_UNIXTIME(ts) AS dt FROM m;
+-------+---------------------+
| val   | dt                  |
+-------+---------------------+
| 12345 | 2007-03-21 10:51:07 |
+-------+---------------------+
1 row in set (0.00 sec)

Another advantage to using INT for ts is that you don't have to convert a DATETIME to an INT before you can partition on that column, and you're not limited to basing your partitioning scheme on years or days in order to use partition pruning to speed up queries.

I don't pretend that this solves all possible issues with the MySQL 5.1 partitioning implementation. We are definitely aware that it can be improved, and we do plan to make improvements for 5.2. Enabling more flexibility for date/time-based partitioning schemes is very high on the TODO list. However, I hope this does show that a lot is already possible, and you do have some options if you're willing to try them.

Jon Stephens
MySQL Documentation Team @ Oracle
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
3635
January 19, 2007 06:55AM
Re: HOW TO: PARTITION USING "TIMESTAMP"
6299
March 20, 2007 07:10PM


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.