CHÚ Ý: Khi ALTER TABLE DROP PARTITION => sẽ mất dữ liệu!!!

Tạo dữ liệu ảo:
delimiter //
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
         while v < 8000000
do
insert into transaction (created_time, price, action, object, object_id, cp_id)
values (adddate('2009-01-01',(rand(v)*36520) mod 3652), 1000, 'download', 'video', 113, 2);
set v = v + 1;
end while;
end
//

delimiter ;
call load_part_tab();

ALTER TABLE transaction
    PARTITION BY RANGE (TO_DAYS(created_time))
    (PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-01-01')),
     PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-04-01')),
     PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-07-01')),
     PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-11-01')),
     PARTITION p5 VALUES LESS THAN (TO_DAYS('2011-01-01')),
     PARTITION p6 VALUES LESS THAN (TO_DAYS('2011-04-01')),
     PARTITION p7 VALUES LESS THAN (TO_DAYS('2011-07-01')),
     PARTITION p8 VALUES LESS THAN (TO_DAYS('2011-11-01'))
    );

# Thêm một PARTITION
ALTER TABLE transaction ADD PARTITION (PARTITION p9 VALUES LESS THAN MAXVALUE);

# Tách một partition
ALTER TABLE TRANSACTION reorganize partition p9 into
(PARTITION p9 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p10 VALUES LESS THAN MAXVALUE);

#ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

ALTER TABLE `transaction` DROP COLUMN `id`, DROP PRIMARY KEY;

# Ko dùng hàm (chả hạn: date(created_time) vs ) vì như thế sẽ ko sử dụng được benefit của partition
EXPLAIN PARTITIONS SELECT DATE(created_time), SUM(price) FROM transaction
WHERE created_time BETWEEN '2010-03-23 00:00:00' AND '2010-04-02 23:59:59'
GROUP BY DATE(created_time)
\G

http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html
#Xóa dữ liệu "cũ"
DELETE FROM transaction
WHERE created_time > date '1995-01-01' and created_time < date '2010-01-01';

#Xóa partition p1 tương ứng
ALTER TABLE transaction DROP PARTITION p1;