I've got a mysql database table that I wish to partition by date, particularly by month &lifier year. However, when new information is added for any new month, I'd rather not have to by hand update the database.
After I initially create my database, I've data in November 09, 12 , 09, Jan 10, etc. Now when Feb begins, I would like a February 10 partition instantly produced. Is possible?
To provide a bit more background on my small problem, there might be hundreds of 1000 records daily put into the DB, so I am also available to other solutions apart from partitioning to prevent querying countless records daily.
yes, you may such as the enhanced partitioning functionality in five.5:
You will find a couple of solutions available, if you prefer a total solution, take a look post out on kickingtyres. It is a fundamental mixture of a saved procedure handling the partition analysis and creation (with a few logging!).
All that you should do is adjust it for your partition type (the example utilizes a bigint partition) and schedule the process to operate using the MySQL Event Scheduler.
I is going to do exactly what the other done (publish a hyperlink) Quick Introdunction to MySQL Partitioning
(take a look at page 50 for the thing you need - partition by date)
Edit: I reread your publish more carefully - if you wish to change partitions by month why not produce a Mysql
event that does that