21

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?

Jeff Storey
  • 56,312
  • 72
  • 233
  • 406
  • I'm not sure if this is the best idea. Each time you alter partitions, you'll be basically rewriting the entire table. This may take quite a lot of time and resources. – Mchl Dec 03 '10 at 16:01
  • @Mchl if you have a better idea, I'd be glad to hear it. You're right, it's pretty intensive (more then I'd like), but if it only runs once a month it's not too bad - especially when I need to be doing regular queries like 'select the 3rd day of last month'. – Benubird Dec 03 '10 at 16:06
  • 5
    "Each time you alter partitions, you'll be basically rewriting the entire table": not at all true. Adding partitions or removing partitions is like creating/dropping a table. Essentially instant. Only modifying an existing partition *that already has data in it* is potentially slow. So if you create a new partition for next month before you insert data in it, that's very fast. – jssebastian May 22 '14 at 22:07

3 Answers3

7

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
0

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

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

user262976
  • 1,994
  • 12
  • 7
  • Would I need to setup a trigger each month to add a new partition for that month? Since I don't want to define all months/years ahead of time... – Jeff Storey Feb 01 '10 at 01:25
  • 1
    @Jeff - I don't believe so, nothing noted in that article anyway. Those are great enhancements, but won't solve your current problem...one I faced when SQL Server added partitions at first as well, PITA for sure. – Nick Craver Feb 01 '10 at 01:32
  • @Nick Craver. The article you posted had stored procedures for doing it. I guess I could use a monthly trigger (or cron job, details TBD) to add a new partition via the stored procedure. Thanks. – Jeff Storey Feb 01 '10 at 02:00
  • 2
    The link is dead – CAFxX Mar 29 '19 at 08:27
0

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

cristian
  • 8,676
  • 3
  • 38
  • 44
  • I is a sugestion. But i think you will end up with an event an a stored procedure to do what you want. – cristian Dec 03 '10 at 16:00