I am a newbie to mysql db. I have a table that I have partitioned by a datetime field
create table status (
SERVERSERIAL NUMERIC(16) NOT NULL,
SERVERNAME VARCHAR(64) NOT NULL,
FIRSTOCCURRENCE DATETIME NULL,
CUSTOMER VARCHAR(64) NULL,
<..many other fields>
PRIMARY KEY ( SERVERSERIAL, SERVERNAME,FIRSTOCCURRENCE )
)
PARTITION BY RANGE COLUMNS (FIRSTOCCURRENCE)
(
PARTITION p2012_3 values less than ('2012-09-30'),
PARTITION p2012_4 values less than ('2012-12-31'),
PARTITION p2013_1 values less than ('2013-03-31'),
PARTITION p2013_2 values less than ('2013-06-30'),
PARTITION p2013_3 values less than ('2013-09-30'),
PARTITION p2013_4 values less than ('2013-12-31'),
PARTITION p2014_1 values less than ('2014-03-31'),
PARTITION p2014_2 values less than ('2014-06-30'),
PARTITION p2014_3 values less than ('2014-09-30')
)
The table currently has 3Mil rows. I want to partition it further by customer since most of my queries also have customer name in the where clause. Have a few questions
a. I think I need to add the Customer field to the primary key. If so, do I need to suspend all writes to the database when I run the alter command?
b. can someone help with the alter statement for adding customer to partitioning in addition to FIRSTOCCURRENCE or some pointers?
Thanks
Ravi