MySQL Forums
Forum List  »  Partitioning

Re: Partition
Posted by: Jonathan Stephens
Date: August 11, 2012 03:37AM

There are multiple issues here.

1. You're trying to use MONTH() on a VARCHAR column. You should declare fldconfirmdate as DATE. (As a best practise, you should declare all of the columns intended to hold dates in YYYY-MM-DD format as DATE.)

2a. You have no primary key. However, you do have a unique key. In such cases, the MySQL partitioning engine treats the unique key as the table's primary key.

2b. The partitioning column must be part of the table's primary key, if it has one. Or, in this case, it must be part of the unique key, since there is a unique key but no primary key.

See https://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-partitioning-keys-unique-keys.html for more detailed info and examples.

I don't see why you don't declare a primary key. IMO every table should have an explicit primary key.

I should also point out that another possible solution to your problem is not to use any primary or unique keys. In this case, you can use any column you like as the partitioning key (subject to the usual rules, of course). But this is not likely to be good for data integrity or performance.

Also, I don't personally recommend Hungarian notation for table and column names. (Actually, I don't recommend Hungarian notation for anything, but that's another story.)

Putting all this together...

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TABLE emp_confirmation (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   emp_id VARCHAR(100) DEFAULT NULL,
    ->   empname VARCHAR(100) DEFAULT NULL,
    ->   joindate DATE DEFAULT NULL,
    ->   confirmdate DATE DEFAULT NULL,
    ->   resigndate DATE DEFAULT NULL,
    ->   status VARCHAR(50) DEFAULT NULL,
    ->   con_status VARCHAR(100) DEFAULT NULL,
    ->   PRIMARY KEY pk (id, confirmdate), 
    ->   KEY k (emp_id, empname, joindate, confirmdate)
    -> )
    -> 
    -> PARTITION BY RANGE( MONTH(confirmdate) )
    -> (
    ->   PARTITION jan VALUES LESS THAN (2),
    ->   PARTITION feb VALUES LESS THAN (3),
    ->   PARTITION mar VALUES LESS THAN (4),
    ->   PARTITION max VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.19 sec)

Further improvements could be made. For example, I don't understand why you have both a record ID and an employee ID, unless it's possible to have multiple records for the same employee? And isn't the employee ID already supposed to be unique? Also, why VARCHAR for the employee ID? Shouldn't that be numeric?

Anyhow, I hope this helps.

cheers

jon.

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

MySQL Dev Zone
MySQL Server Documentation
Oracle



Edited 1 time(s). Last edit at 08/11/2012 04:11AM by Jon Stephens.

Options: ReplyQuote


Subject
Views
Written By
Posted
2766
August 10, 2012 12:37AM
Re: Partition
1682
August 11, 2012 03:37AM
1482
August 19, 2012 03:18PM


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.