CODEWITHSUNDEEP
mysqldatabaseperformancepartitioning
Jeff Storey
Jeff Storey

Reputation: 57212

mysql database automatic partitioning

I have a mysql database table that I want to partition by date, particularly by month & year. However, when new data is added for a new month, I don't want to need to manually update the database.

When I initially create my database, I have data in Nov 09, Dec 09, Jan 10, etc. Now when February starts, I'd like a Feb 10 partition automatically created. Is this possible?

Upvotes: 21

Views: 30817

Answers (3)

Nick Craver
Nick Craver

Reputation: 630627

There are a few solutions out there, if you want a total solution, check this post out on kickingtyres. It's a basic combination of a stored procedure handling the partition analysis and creation (with some logging!).

All you need to do is adjust it to your partition type (the example uses a bigint partition) and schedule the procedure to run with the MySQL Event Scheduler.

Upvotes: 7

cristian
cristian

Reputation: 8744

I will do what the other done (post a link) Quick Introdunction to MySQL Partitioning

(look at page 50 for what you need - partition by date) Edit: I reread your post more carefully - if you want to change partitions by month why don't you create a Mysql event that does that

Upvotes: 0

user262976
user262976

Reputation: 2074

yes, you might like the enhanced partitioning functionality in 5.5:

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

Upvotes: 0

Related Questions