PARTITION Maintenance in MySQL

First, my Opinions on PARTITIONing
AUTO_INCREMENT in PARTITION
The Maintenance Situation
The Problem
High Level View of the Code
Why?
When to do the ALTERs?
Variants
Detailed code
Postlog

First, my Opinions on PARTITIONing

Taken from Rick's RoTs - Rules of Thumb for MySQL
    ⚈  #1: Don't use PARTITIONing until you know how and why it will help.
    ⚈  Don't use PARTITION unless you will have >1M rows
    ⚈  No more than 50 PARTITIONs on a table (open, show table status, etc, are impacted) (fixed in 5.6.6?)
    ⚈  PARTITION BY RANGE is the only useful method.
    ⚈  SUBPARTITIONs are not useful.
    ⚈  The partition field should not be the field first in any key.
    ⚈  It is OK to have an AUTO_INCREMENT as the first part of a compound key, or in a non-UNIQUE index.

It is so tempting to believe that PARTITIONing will solve performance problems. But it is so often wrong.

PARTITIONing splits up one table into several smaller tables. But table size is rarely a performance issue. Instead, I/O time and indexes are the issues.

Perhaps the most common use case where PARTITIONing shines is the the dataset where "old" data is deleted from the table. RANGE PARTITIONing by day (or other unit of time) lets you do a nearly instantaneous DROP PARTITION plus REORGANIZE PARTITION instead of a much slower DELETE.

An AUTO_INCREMENT column must be the first column in some index. (That lets the engine find the 'next' value when opening the table.) It does not have to be the only field, nor does it have to be PRIMARY or UNIQUE. If it is not UNIQUE, you _could_ INSERT a duplicate id if you _explicitly_ provide the number.

AUTO_INCREMENT in PARTITION

    ⚈  For AUTO_INCREMENT to work (in any table), it must be the first field in some index. Period.
    ⚈  AUTO_INCREMENT need not be UNIQUE. What you lose: prevention of explicitly inserting a duplicate id. (This is rarely needed, anyway.)
Examples (where id is AUTO_INCREMENT):
    ⚈  PRIMARY KEY (...), INDEX(id)
    ⚈  PRIMARY KEY (...), UNIQUE(id, partition_key) -- not useful
    ⚈  INDEX(id), INDEX(...) (but no UNIQUE keys)
    ⚈  PRIMARY KEY(id), ... -- works only if id is the partition key (not very useful)

The Maintenance Situation

You have a large table that is growing on one end and being pruned on the other. Examples include news, logs, and other transient information. PARTITION BY RANGE is an excellent vehicle for such a table.
⚈ DROP PARTITION is much faster than DELETE. (This is the big reason for doing this.)
⚈ Queries often limit themselves to 'recent' data, thereby taking advantage of "partition pruning".

Depending on the type of data, and how long before it expires, you might have daily or weekly or hourly (etc) partitions.

The Problem

There is no simple SQL statement to "drop partitions older than 30 days" or "add a new partition for tomorrow". It would be tedious to do this by hand every day.

High Level View of the Code

ALTER TABLE tbl
    DROP PARTITION from20120314;
ALTER TABLE tbl
    REORGANIZE PARTITION future INTO
        from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
        future    VALUES LESS THAN MAXVALUE;

After which you have...

    CREATE TABLE tbl (
        dt DATETIME NOT NULL,  -- or DATE
        ...
        PRIMARY KEY (..., dt),
        UNIQUE KEY (..., dt),
        ...
    )
    PARTITION BY RANGE (TO_DAYS(dt)) (
        start     VALUES LESS THAN (0),
        from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16')),
        from20120316 VALUES LESS THAN (TO_DAYS('2012-03-17')),
        ...
        from20120414 VALUES LESS THAN (TO_DAYS('2012-04-15')),
        from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
        future       VALUES LESS THAN MAXVALUE
    );