All columns used in the partitioning expression for a partitioned table must be part of every unique/PRIMARY key that the table may have. e.g.
UNIQUE KEY fldattendanceid (fldattendanceid, fldassigndate )
Few other mistakes:
>PARTITION BY RANGE (fldassigndate)
it should be like:
PARTITION BY RANGE (TO_DAYS(fldassigndate))
>fldassigndate varchar(20) DEFAULT NULL,
It should be like:
fldassigndate DATE DEFAULT NULL,
And don't forget to catch for invalid dates,
>PARTITION invalid_date VALUES LESS THAN (0)
(Bug#49754: Partitioning by RANGE with TO_DAYS always includes first partition when pruning)
Ok, the correct syntax is as follow:
CREATE TABLE tblattendancesetup (
fldattendanceid int(11) NOT NULL AUTO_INCREMENT,
flddept varchar(100) DEFAULT NULL,
fldemployee varchar(100) DEFAULT NULL,
fldintime varchar(20) DEFAULT NULL,
fldouttime varchar(20) DEFAULT NULL,
fldlateafter varchar(20) DEFAULT NULL,
fldearlybefore varchar(20) DEFAULT NULL,
fldweekoff varchar(20) DEFAULT NULL,
fldshiftname varchar(20) DEFAULT NULL,
fldassigndate DATE DEFAULT NULL,
fldfromdate VARCHAR(20) DEFAULT NULL,
fldtodate varchar(20) DEFAULT NULL,
fldrefid varchar(20) DEFAULT NULL,
primary KEY fldattendanceid (fldattendanceid,fldassigndate) ,
KEY in_attendancesetup (fldemployee,fldintime,fldouttime,fldlateafter,fldearlybefore,fldfromdate,fldtodate,fldattendanceid),
KEY i_emp_tmp (fldemployee),
KEY i_emp_attendance (fldemployee)
)
PARTITION BY RANGE (to_days(fldassigndate))
(PARTITION invalid_date VALUES LESS THAN (0),
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p_Nov VALUES LESS THAN MAXVALUE );
>KEY in_attendancesetup (fldemployee,fldintime,fldouttime,fldlateafter,fldearlybefore,fldfromdate,fldtod
Do you really require such a long secondary index? Provide 'SELECTs' that you would be using against this table.