To increase the Query execution Performance in MySQL , we found the solution to do the data partitioning on the table.
What is Data Partitioning?
-
Partition enables you to distribute portions of individual tables across a file system according to rules which you can set largely as needed.
-
In effect, different portions of a table are stored as separate tables in different locations. The user-selected rule by which the division of data is accomplished is known as a partitioning function.
-
Partitioning allows you to store parts of your table in their own logical space. With partitioning, you want to divide up your rows based on how you access them. If you partition your rows and you are still hitting all the partitions, it does you no good.
-
The goal is that when you query, you will only have to look at a subset of the data to get a result, and not the whole table.
Advantages of Partitioning
-
SQL statements executed over partitioned tables return results faster.
-
Increses the query execution performance.
-
With the use of MYISAM Storage engine insertion operation on table is faster.
Types of Data Partitioning
There are 4 different types are partition
-
Range partition
Each partition contains rows mapping to a specific set (range) of values. Range partitioning is useful when frequently running queries that depend directly on the column used for partitioning the table, or for quickly deleting old data by simply dropping a table.
-
List partition
List partitioning is similar to range partitioning. The main difference is that this partitioning mode allows data to be segmented based on a pre-defined list of values that are specified by the DBA, rather than a set of contiguous ranges of values.
-
Hash partition
In Hash partitioning, a partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be inserted into the table. The function may consist of any expression valid in MySQL that yields a non-negative integer value. An extension to this type, LINEAR HASH .
-
Key partition
Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server.
Limitations in Data Partition
-
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
-
The following constructs are not permitted in partitioning expressions:
– Stored procedures, stored functions, UDFs, or plugins.
-Declared variables or user variables.
-
As it locks the table we are not able to insert the data in table while partition.
-
There are only some specific functions we can be used in partitioning expressions.Refer below manual to see which functions are they:
http://dev.mysql.com/doc/mysql-reslimits-excerpt/5.5/en/partitioning-limitations-functions.html
-
If you are using InnoDB Storage engine there is limitation we cannot use the foreign key as well they don’t have column which referes to foreign key.
-
The maximum number of partitions should be 1024.
Steps to partition the table
By two ways you can use the partition :
-
Partition at the time of table creation.
For Example :
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id INT NOT NULL,
department_id INT NOT NULL
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
2. Partition on existing table.
ALTER TABLE employees
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN MAXVALUE);
In this way you can partition the table in MySQL .
Thanks,
Sayali Mahale.
Best Open Source Business Intelligence Software Helical Insight is Here
