mysql> analyze table alert_received; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | test.alert_received | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> show indexes from alert_received; +----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | alert_received | 0 | PRIMARY | 1 | alr_guid | A | 217038 | NULL | NULL | | BTREE | | | alert_received | 0 | PRIMARY | 2 | created_timestamp | A | 217038 | NULL | NULL | | BTREE | | | alert_received | 1 | cus_guid | 1 | cus_guid | A | 10 | NULL | NULL | YES | BTREE | | | alert_received | 1 | created_time_bay_num_idx | 1 | sct_guid | A | 144 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_bay_num_idx | 2 | bay_number | A | 144 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_bay_num_idx | 3 | created_timestamp | A | 217038 | NULL | NULL | | BTREE | | | alert_received | 1 | basic_report_idx | 1 | created_timestamp | A | 217038 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_idx | 1 | sct_guid | A | 14 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_idx | 2 | bay_guid | A | 144 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_idx | 3 | created_timestamp | A | 217038 | NULL | NULL | | BTREE | | | alert_received | 1 | alt_guid | 1 | alt_guid | A | 10 | NULL | NULL | | BTREE | | | alert_received | 1 | bay_guid | 1 | bay_guid | A | 216 | NULL | NULL | | BTREE | | +----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ 12 rows in set (0.00 sec) [everything is fine, now try to partition it] mysql> alter table alert_received partition by range (to_days(created_timestamp)) -> ( -> partition p200610 VALUES LESS THAN (to_days('2006-10-01')), -> partition p200611 VALUES LESS THAN (to_days('2006-11-01')), -> partition p200612 VALUES LESS THAN (to_days('2006-12-01')), -> PARTITION p200701 VALUES LESS THAN (to_days('2007-01-01')), -> PARTITION p200702 VALUES LESS THAN (to_days('2007-02-01')), -> PARTITION p200703 VALUES LESS THAN (to_days('2007-03-01')), -> PARTITION p200704 VALUES LESS THAN (to_days('2007-04-01')), -> PARTITION p200705 VALUES LESS THAN (to_days('2007-05-01')), -> PARTITION p200706 VALUES LESS THAN (to_days('2007-06-01')), -> PARTITION p200707 VALUES LESS THAN (to_days('2007-07-01')), -> PARTITION p200708 VALUES LESS THAN (to_days('2007-08-01')), -> PARTITION p200709 VALUES LESS THAN (to_days('2007-09-01')), -> PARTITION p200710 VALUES LESS THAN (to_days('2007-10-01')), -> PARTITION p200711 VALUES LESS THAN (to_days('2007-11-01')), -> PARTITION p200712 VALUES LESS THAN (to_days('2007-12-01')), -> PARTITION p200801 VALUES LESS THAN (to_days('2008-01-01')), -> PARTITION p200802 VALUES LESS THAN (to_days('2008-02-01')), -> PARTITION p200803 VALUES LESS THAN (to_days('2008-03-01')), -> PARTITION p200804 VALUES LESS THAN (to_days('2008-04-01')), -> PARTITION p200805 VALUES LESS THAN (to_days('2008-05-01')), -> PARTITION p200806 VALUES LESS THAN (to_days('2008-06-01')), -> PARTITION p200807 VALUES LESS THAN (to_days('2008-07-01')), PARTITION p200904 VALUES LESS THAN (to_days('2009-04-01')), -> PARTITION p200808 VALUES LESS THAN (to_days('2008-08-01')), -> PARTITION p200809 VALUES LESS THAN (to_days('2008-09-01')), -> PARTITION p200810 VALUES LESS THAN (to_days('2008-10-01')), -> PARTITION p200811 VALUES LESS THAN (to_days('2008-11-01')), -> PARTITION p200812 VALUES LESS THAN (to_days('2008-12-01')), -> PARTITION p200901 VALUES LESS THAN (to_days('2009-01-01')), -> PARTITION p200902 VALUES LESS THAN (to_days('2009-02-01')), -> PARTITION p200903 VALUES LESS THAN (to_days('2009-03-01')), -> PARTITION p200904 VALUES LESS THAN (to_days('2009-04-01')), -> PARTITION p200905 VALUES LESS THAN (to_days('2009-05-01')), -> PARTITION pfuture VALUES LESS THAN MAXVALUE); Query OK, 213868 rows affected (11.69 sec) Records: 213868 Duplicates: 0 Warnings: 0 mysql> analyze table alert_received; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | test.alert_received | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0.04 sec) mysql> show indexes from alert_received; +----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | alert_received | 0 | PRIMARY | 1 | alr_guid | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 0 | PRIMARY | 2 | created_timestamp | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | cus_guid | 1 | cus_guid | A | 215088 | NULL | NULL | YES | BTREE | | | alert_received | 1 | created_time_bay_num_idx | 1 | sct_guid | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_bay_num_idx | 2 | bay_number | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_bay_num_idx | 3 | created_timestamp | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | basic_report_idx | 1 | created_timestamp | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_idx | 1 | sct_guid | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_idx | 2 | bay_guid | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | created_time_idx | 3 | created_timestamp | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | alt_guid | 1 | alt_guid | A | 215088 | NULL | NULL | | BTREE | | | alert_received | 1 | bay_guid | 1 | bay_guid | A | 215088 | NULL | NULL | | BTREE | | +----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ 12 rows in set (0.30 sec)