Re: How can I decide which partition type can I chose
mysql> show create table PI\G
*************************** 1. row ***************************
Table: PI
Create Table: CREATE TABLE "PI" (
"ID" varchar(50) NOT NULL DEFAULT '0',
"P_ID" varchar(50) NOT NULL DEFAULT '0',
"R_ID" varchar(50) NOT NULL DEFAULT '0',
"M_ID" varchar(50) DEFAULT NULL,
"M_R_ID" varchar(50) DEFAULT NULL,
"NAME" varchar(200) DEFAULT NULL,
"MSG" longtext,
"START_TIME" decimal(16,0) DEFAULT NULL,
"END_TIME" decimal(16,0) DEFAULT NULL,
"DESCRIPTION" varchar(100) DEFAULT NULL,
"ERROR_MSG" text,
"TYPE" varchar(100) DEFAULT NULL,
"USER" text,
"ORGANIZATION" varchar(240) DEFAULT NULL,
"STATUS" varchar(15) DEFAULT NULL,
"IS_ARCHIVED" tinyint(4) DEFAULT NULL,
PRIMARY KEY ("ID"),
KEY "IX_PI_START_TIME" ("ORGANIZATION","START_TIME"),
KEY "IX_PI_END_TIME" ("ORGANIZATION","END_TIME"),
KEY "IX_NAME" ("ORGANIZATION","NAME","STATUS"),
KEY "IX_P_ID" ("P_ID")
)
/*!50100 PARTITION BY KEY (INSTANCE_ID)
PARTITIONS 10 */
1 row in set (0.00 sec)
The archive here means: records in this table are exported to a CSV file and the records from this table are deleted based on some condition (mostly start_time and name).
mysql> SHOW TABLE STATUS LIKE 'PI'\G
*************************** 1. row ***************************
Name: PI
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 361063
Avg_row_length: 3891
Data_length: 1405140992
Max_data_length: 0
Index_length: 225296384
Data_free: 28311552
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: partitioned
Comment:
1 row in set (0.07 sec)
There is always a separate query based on the ID to fetch the MSG value. ID is a GUID (Ex:- 005056C0-0008-11DF-F7E3-1AEE42629F53)