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)