Re: Proactive partitioning by Week

[Jonathan Stephens]

You can't partition by RANGE (which I assume is what you're after) explicitly on a DATE column - you need to use a function that acts on a DATE returns an integer value. Something like WEEK() or YEARWEEK() will work, but only the functions TO_DAYS() and YEARS() are optimised in MySQL 5.1. See http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html for more info.

You also need to take into consideration the rules regarding primary/unique keys and partitioning keys: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html

Thanks!

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation
Oracle