Partitioning with Dates in MySQL 5.1
by Robin Schumacher
Many of you have responded very positively on the first and second partitioning articles that I wrote some time back. It’s clear that a lot of you are excited about the upcoming MySQL 5.1 release, and in particular, the new data partitioning feature that’s being offered. That’s no surprise because, as we covered in the previous two articles, partitioning has some excellent performance and management benefits for anyone who’s building data warehouses or other large databases with MySQL.
However, I’ve gotten some feedback indicating a disappointment that MySQL 5.1 doesn’t support partitioning on a native date/time column. Given that many data warehouses and other large databases try and segment data by some date-based algorithm – especially via the RANGE partitioning style – it’s understandable that a number of you are wondering if partitioning in 5.1 will meet the needs of your particular business intelligence application that relies on time-based functions.
It’s true that in MySQL 5.1 the actual partitioning key (the element used by the MySQL partition engine to horizontally divide the rows across the various partitions defined on a table or index) must be an integer value. But that doesn’t mean you can’t partition by date in MySQL 5.1 – it just requires the proper selection between a couple of MySQL date functions and you’ll be in business.
Let’s take a look at how to properly partition by dates in MySQL 5.1 and also showcase how not to try and partition by date, so you won’t waste time and effort on something that doesn’t give you the results that you’re looking for.
http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html