Partition on range date with/without primary key
Posted by:
Dan Shao
Date: April 20, 2009 08:35AM
I'm new to partitioning and I am having some problems with partitioning.
This is my data set:
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| data_id | int(11) | NO | PRI | NULL | |
| file_id | int(11) | NO | MUL | NULL | |
| latitude | float(9,5) | YES | MUL | NULL | |
| longitude | float(9,5) | YES | | NULL | |
| date_time | datetime | NO | MUL | NULL | |
| depth | float | NO | | NULL | |
| station | varchar(50) | YES | | NULL | |
| hide | tinyint(4) | NO | | NULL | |
| pub | tinyint(4) | NO | | NULL | |
| etopo2 | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
Now, I understand that the primary key needs to be part of the partitioning function. However, I would like to create partitions based on the year of the date_time column. Therefore, as I understand it, I can just drop the primary key and apply my partitioning on the year. Eg.
mysql> alter table data_info partition by range (year(date_time)) (partition p0 values less than (1997), partition p1 values less than (1999), partition p2 values less than (2001), partition p3 values less than (2003), partition p4 values less than (2005), partition p5 values less than (2007), partition p6 values less than maxvalue);
However, I would also like to take advantage of partition pruning. However, running this statement below shows otherwise:
mysql> explain partitions select * from data_info where year(date_time) < 1997;
+----+-------------+-----------+----------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+----------------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | data_info | p0,p1,p2,p3,p4,p5,p6 | ALL | NULL | NULL | NULL | NULL | 366732 | Using where |
+----+-------------+-----------+----------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
So I have a couple of questions:
1. Why is it not just looking within p0?
2. How important is it to have a Primary key? Will dropping data_id as a primary key affect anything? What are the benefits and usage of a primary vs unique key.
3. How can I successfully set up partitions so that I can take advantage of partition pruning.
Thanks
- Dan