mysql database automatic partitioning

Question

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?

To give a little more background on my problem, there could be several hundred thousand records per day added to the DB, so I'm also open to other solutions other than partitioning to avoid querying millions of records per day.

Thanks, Jeff

Answer 1

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

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

Answer 2

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.

Answer 3

Have a look at my solution to this problem:

http://www.kickingtyres.com/blog/2010/01/14/mysql-partitioning-automated-management/

[edit] I should read the other answers in more depth, I didn't realise the first answer linked to my own blog :)

Answer 4

updated the site, and since this post sends me a lot of traffic, I thought it fair to share the new URL for the relevant blogpost.

http://www.kickingtyres.com/MySQL-Partitioning-Automated-Management/