Re: How can I decide which partition type can I chose

[Suresh Chary]

Hi John,
This is my table structure:
+---------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| ID | varchar(50) | NO | PRI | 0 | |
| P_ID | varchar(50) | NO | MUL | 0 | |
| R_ID | varchar(50) | NO | | 0 | |
| M_ID | varchar(50) | YES | | NULL | |
| M_R_ID | varchar(50) | YES | MUL | NULL | |
| NAME | varchar(200) | YES | | NULL | |
| MSG | longtext | YES | | NULL | |
| START_TIME | decimal(16,0) | YES | | NULL | |
| END_TIME | decimal(16,0) | YES | | NULL | |
| DESCRIPTION | varchar(100) | YES | | NULL | |
| ERROR_MSG | text | YES | | NULL | |
| TYPE | varchar(100) | YES | | NULL | |
| USER | text | YES | | NULL | |
| ORGANIZATION | varchar(240) | YES | MUL | NULL | |
| STATUS | varchar(15) | YES | | NULL | |
| IS_ARCHIVED | tinyint(4) | YES | | NULL | |
+---------------------+---------------+------+-----+---------+-------+
There is no datetime field in my table. Date is captured in milliseconds (start_time). We need to archive the old data as well as it gets accumulated.
I could understand that to partition a table, we need to choose a column from the primary key if it has one (in my it has "ID"). But the 3 types of partitions (Range, List, Hash) do not allow to create a partition on non-integer columns. So the only choice left for me is to partition by Key. Some how I am not seeing any benefit with this type of partition.
But we need a solution to partition the data because of the size the table grows. Please help.