MySQL partitioning query on timestamp

Question

I running MySQL 5.1.30 on Solaris 10. As I have a table which I partition into 12 according to each month.

The table structure as follow

mysql> desc my_events;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| event_id  | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| timestamp | timestamp    | NO   | PRI | NULL    |                | 
| object    | varchar(10)  | YES  |     | NULL    |                | 
| info      | text         | YES  |     | NULL    |                | 
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test11' AND PARTITION_METHOD = 'RANGE';
+----------------+------------+----------------------+-----------------------+
| PARTITION_NAME | TABLE_ROWS | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------+----------------------+-----------------------+
| p001           |          0 | to_days(timestamp)   | 733773                | 
| p002           |          0 | to_days(timestamp)   | 733804                | 
| p003           |      20863 | to_days(timestamp)   | 733832                | 
| p004           |     269336 | to_days(timestamp)   | 733863                | 
| p005           |    3094672 | to_days(timestamp)   | 733893                | 
| p006           |    2639348 | to_days(timestamp)   | 733924                | 
| p007           |     314010 | to_days(timestamp)   | 733954                | 
| p008           |          0 | to_days(timestamp)   | 733985                | 
| p009           |          0 | to_days(timestamp)   | 734016                | 
| p010           |          0 | to_days(timestamp)   | 734046                | 
| p011           |          0 | to_days(timestamp)   | 734077                | 
| p012           |          0 | to_days(timestamp)   | 734107                | 
+----------------+------------+----------------------+-----------------------+
12 rows in set (0.05 sec)

When I want to query for particular range of days.

mysql> DESCRIBE PARTITIONS SELECT * FROM my_events where timestamp > '2009-03-01' and timestamp < '2009-03-30';
+----+-------------+-------------------+-------------------------------------------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table             | partitions                                                  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------------+-------------------------------------------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | my_events | p001,p002,p003,p004,p005,p006,p007,p008,p009,p010,p011,p012 | ALL  | NULL          | NULL | NULL    | NULL | 6338229 | Using where | 
+----+-------------+-------------------+-------------------------------------------------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

It query all the partitions when I use type timestamp for timestamp. However when I change the type to datetime for timestamp, it query only 1 partition (p004).

mysql> DESCRIBE PARTITIONS SELECT * FROM my_events where timestamp > '2009-03-01' and timestamp < '2009-03-30';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | my_events | p004       | ALL  | NULL          | NULL | NULL    | NULL | 6338229 | Using where | 
+----+-------------+-------------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

How can I query only 1 partition but with using type timestamp for timestamp?

Answer 1

I've just been researching this and the short answer is that you can't according to http://bugs.mysql.com/bug.php?id=24245

"pruning is not expected to work for tables partitioned on a TIMESTAMP column, and you should use a DATETIME or DATE column for this instead."

Answer 2

this is a good article http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

personally i'd take an innodb clustered composite PK index over a partition until my data storage requirements got silly - really silly