MySQL Partitions.

MySQL Partitions is a New Feature of MySQL 5.1, This is the most awaited feature of MySQL.
I am very happy to find this feature in MySQL. because Partitioning is a way of pre-organizing table storage.

Benifits: Partitioning can be useful in speeding up selects on large tables.

Creating Partitioned Tables:
The way creating partitioned tables is the PARTITION clause
of CREATE TABLE.

Here's the rule which we have to remember at time of partitioning tables.

1.> If a table has a primary key, then any columns used in the partitioning expression must be part of the primary key.

2.>If a table has no primary key, but does have one or more unique keys, then any columns used in the partitioning expression must be part of the (first) unique key.

3.>Regular indexes have no effect on what columns may or may not be used in a partitioning expression. This means that if you need an index on the id column (or any other column) to speed up searches, you can define one without getting into trouble.

4. >Partitioned tables do not support FULLTEXT indexes, including tables that use the MYISAM storage engine.

There are four type of partitioning,Let me Explain with an Example.......

1. Range: Rows with column or expression values falling within a specified range are assigned to a given partition.
See following example it's non-partitioned table

CREATE TABLE employee (

id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(30),
emp_designation varchar(45),
joining_date DATE
) ENGINE = MYISAM;

Here's the Range partitioning for this table.

CREATE TABLE employee (
id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(30),
emp_designation varchar(45),
joining_date DATE
) ENGINE = MYISAM
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN(2000),
PARTITION p1 VALUES LESS THAN(3000),
PARTITION p2 VALUES LESS THAN(4000),
PARTITION p3 VALUES LESS THAN(5000)
);

Any row having an id whose value is less than 2000 is stored in partition p0. The next partition is named p1, and stores rows whose id column values are less than 2000 and p2 stores values less than 3000 and so on.

Note: I try to insert a row having order ID number 5000 or higher?" The answer is that such an insert fails:

ERROR : Table has no partition for value 5000

To overcome this error, there's a more general way to accomplish this, as you can see here:

CREATE TABLE employee (
id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(30),
emp_designation varchar(45),
joining_date DATE )
ENGINE = MYISAM
PARTITION BY RANGE(id)
( PARTITION p0 VALUES LESS THAN(2000),
PARTITION p1 VALUES LESS THAN(3000),
PARTITION p2 VALUES LESS THAN(4000),
PARTITION p3 VALUES LESS THAN(5000),
PARTITION p5 VALUES LESS THAN(MAXVALUE)
);

so in this case partition p5 can conatin "the greatest possible value .