Creating partitions on a production database I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.

I have two tables that involve a composite key on columns:

Bill_Num
Bill_Date

I want to create Partition on Bill_Date. The table consists records of four years. I want to know how a new partitioned table will accommodate future years? I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name?
I want to know how a new partitioned table will accommodate future years?


I'm fairly sure, according to the 5.1 documentation, that you'll have to alter table add partition to handle future years. 


  I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name


Are you needing to use the 'old' table after you get this set up? If not, I would follow these steps:


Create partitioned table as you want it, under table name 'mytable_new'
INSERT INTO mytable_new SELECT * FROM mytable
RENAME TABLE mytable TO mytable_old
RENAME TABLE mytable_new TO mytable


This should avoid the need to alter your queries. If your table is extremely write-heavy (I assume it must be if you have the need to partition it!), you will probably want to have a maintenance timeframe where it's offline until the operation is complete.

Hope this helps.