MySQL View and new table partitioning
1. View Table partition
SELECT
Partition_name part,
Partition_expression expr,
Partition_description Descr,
From_days (partition_description) Lessthan_sendtime,
Table_rows
From
Information_schema.partitions
WHERE
Table_schema = SCHEMA ()
and Table_name= ' Td_sendmessagelog '; ---here is the table name
2. Add Table partition
ALTER TABLE td_sendmessagelog ADD PARTITION (
PARTITION p20150210 VALUES Less THAN (to_days (' 2015-02-10 ')),
PARTITION p20150220 VALUES Less THAN (to_days (' 2015-02-20 ')),
PARTITION p20150301 VALUES Less THAN (to_days (' 2015-03-01 ')),
PARTITION p20150310 VALUES Less THAN (to_days (' 2015-03-10 ')),
PARTITION p20150320 VALUES Less THAN (to_days (' 2015-03-20 ')),
PARTITION p20150401 VALUES Less THAN (to_days (' 2015-04-01 ')),
PARTITION p20150410 VALUES Less THAN (to_days (' 2015-04-10 ')),
PARTITION p20150420 VALUES Less THAN (to_days (' 2015-04-20 ')),
PARTITION p20150501 VALUES Less THAN (to_days (' 2015-05-01 ')),
PARTITION Pmax VALUES less THAN (maxvalue)
);
Note: To create a table partition, specify the corresponding column. In the above example, a time column (Sendtime) is used.
Create the table procedure as follows (note the specified primary key):
CREATE TABLE Td_sendmessagelog
(
ID int NOT NULL,
Sendtime datetime NOT NULL
Primary KEY (Id,sendtime)
)
To create an index:
Create INDEX index_sid_sendtime on Td_sendmessagelog
(
Sid
Sendtime
);
MySQL View and new table partitioning