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
MySQL Dev Zone
MySQL Server Documentation
Oracle