MySQL Forums
Forum List  »  Partitioning

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition on range date with/without primary key
6033
April 20, 2009 08:35AM


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.