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?

Improve this question
3
  • 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
    Commented Dec 3, 2010 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
    Commented Dec 3, 2010 at 16:06
  • 6
    "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
    Commented May 22, 2014 at 22:07

3 Answers 3

Reset to default
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.

Improve this answer
3
  • 1
    I like the concept of the stored procedure for the partition creation.
    – Jeff Storey
    Commented Feb 1, 2010 at 1:30
  • 8
    kickingtyres link is broken.
    – Soma Holiday
    Commented May 5, 2014 at 11:17
  • Dead links aren't helpful.
    – Fuzzy
    Commented May 6, 2016 at 15:11
0

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

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

Improve this answer
4
  • 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
    Commented Feb 1, 2010 at 1: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
    Commented Feb 1, 2010 at 1: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
    Commented Feb 1, 2010 at 2:00
  • 3
    The link is dead
    – CAFxX
    Commented Mar 29, 2019 at 8: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

Improve this answer
1
  • I is a sugestion. But i think you will end up with an event an a stored procedure to do what you want.
    – cristian
    Commented Dec 3, 2010 at 16:00

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.