Re: Partition

[Jonathan Stephens]

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.