MySQL Forums
Forum List  »  Partitioning

Re: How can I decide which partition type can I chose
Posted by: Suresh Chary
Date: September 14, 2010 06:27AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How can I decide which partition type can I chose
1371
September 14, 2010 06:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.