Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
I do not have an answer for you, but you may want to watch the question I had posed just moments ago, since they are very much related: dba.stackexchange.com/questions/4134/… –  CenterOrbit Jul 26 '11 at 19:48
Thanks for adding the tag "partitioning" @DTest, I had tried to add a tag similar to that, but I dont have 150 points on this site to add tags. I also added "database-administration" as a tag because this is a live production site, would require administration of some degree IMHO –  CenterOrbit Jul 26 '11 at 21:44
add comment

2 Answers

up vote 4 down vote accepted

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:

  1. Create partitioned table as you want it, under table name 'mytable_new'
  2. INSERT INTO mytable_new SELECT * FROM mytable
  3. RENAME TABLE mytable TO mytable_old
  4. 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.

share|improve this answer
Straightforward and Concise. +1 !!! –  RolandoMySQLDBA Jul 27 '11 at 15:47
add comment

I have recently worked on a MySQL database system which had numerous tables running into the billions of rows of data, which was fairly write heavy in usage. A slight advantage I had was the tables only needed to contain about 5 years worth of data - this got me thinking. Could I create a partitioning scheme which allowed the years to wrap nicely at the end of the partition? I also had the advantage that many of the queries performed were on either quarterly or yearly data - be that calendar or financial.

What I can up with was the following partitioning function against the date field - this did require having the date field in the primary key, but that wasn't much of a hardship for the benefits it produced...

PARTITION BY HASH (( YEAR(`Period`) + MONTH(`Period`)*4 )) PARTITIONS 24

This create a series of 24 partitions, each containing 3 consecutive months of data, spanning a six year period. Any query accessing a single quarters data (Jan to Mar) would only access one partition. The majority of writes were also against a single partition.

As the years rolled on, and wrapped back to the beginning, the early partitions contain more data than the rest, until the earliest year of data is culled from the table, causing them to drop back to the same rough row-counts as their siblings.

Hope this helps someone?


share|improve this answer
Hey, this is not just an answer. This is a case study. Thank you (and a hearty +1) for sharing this. I am sure it will help someone. I also noticed this was the very first answer you submitted. Great First Answer !!!! Please get the word out for other MySQL DBAs and Developers to join the DBA Stack Exchange. Cheers !!! –  RolandoMySQLDBA Aug 10 '11 at 22:29
Hi Rolando, It wasn't really supposed to be an answer, just a bit of advice / help from someone who's dealt with large date dependant data tables! :) Thanks for the +1. This may be my first post here, but I've done a few on stackoverflow, and only recently found out about this beta site! –  Dave Rix Aug 12 '11 at 7:43
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.