Partitioning a table in Mysql

Partitioning a table makes your query execution faster. As your database will look for a record in near vicinity because you have partitioned the table.
See in this way:

You have 100000 of records in a table call "COLLTable" where Time is a column.
Now if you will look for a record where Time is like this:-'2008-06-12 12:12:23'
Then Database will have to look in all 100000 rows, but if you will do the partition based on (assume)month wise, it will look only in June month table partition.


Partitioning can be done on 4 basis:-
* range
* list
* hash
* key


But remember, Partitioning in mysql is available in version 5.1.x onwards.
Anyway you can check this by firing query:

*) SHOW VARIABLES LIKE '%partition%';

To see the mysql version:-
*) select version();


Now how to achieve partition:-

//If you are creating new table, fire this query, 3-months in one partition, I assume Time is one column here in the table:
*) CREATE TABLE COLLTable(
Id INT not null,
Time Timestamp NULL,
DISK_IOWRITE FLOAT not null,
PRIMARY KEY(Id,Time) )

PARTTIION BY LIST (MONTH(Time)) (
PARTITION p1 VALUES IN (3,4,5),
PARTITION p2 VALUES IN (6,7,8),
PARTITION p3 VALUES IN (9,10,11),
PARTITION p4 VALUES IN (12,1,2)
);


//If you have already a table with "Time" column, add partitions there, use following query:-
*) Alter table COLLTable PARTITION BY LIST (MONTH(Time)) (PARTITION P1 VALUES IN (1),PARTITION P2 VALUES IN (2),PARTITION P3 VALUES IN (3),PARTITION P4 VALUES IN (4),PARTITION P5 VALUES IN (5),PARTITION P6 VALUES IN (6),PARTITION P7 VALUES IN (7),PARTITION P8 VALUES IN (8),PARTITION P9 VALUES IN (9),PARTITION P10 VALUES IN (10),PARTITION P11 VALUES IN (11),PARTITION P12 VALUES IN (12));

//Now check this details, The entry goes to INFORMACTION_SCHEMA database and in PARTITION table:-
*) select TABLE_SCHEMA, TABLE_NAME,TABLE_ROWS, PARTITION_NAME, PARTITION_EXPRESSION from information_schema.PARTITIONS where TABLE_NAME='COLLTable';


//You can drop the partition also, but remember after dropping a partition the data will also be removed from that partition.
//Mysql creates different data files for different partitions, so dropping a partition will drop that file too.
//If you don't have "Time" as column, you can create partition for "Range" that takes integer type of values like:-

CREATE TABLE CollTable ( empId integer not null, salary float ) PARTITION BY RANGE (empId)
(
PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (10000)
);


//End