One of the important concepts in database management is Partitioning. Partitioning is the concept by which storage of tables are pre-organized to attain efficiency. That is in other words the rows present in a table are stored across different disks or fields to achieve optimization and performance improvement.
How to achieve this concept in MYSQL
Database statements generally get divided into three broad categories namely:
- DDL – Data definition Language
- DML - Data Manipulation Statements
- TCL - Transaction Control Statements
For having this partitioning concept introduced the syntax of Data Manipulation Statements like SELECT, UPDATE, INSERT, DELETE do not gets changed. But the data definition statements like CREATE TABLE and ALTER TABLE have some additions in the syntx to have the partitioning concept added to the tables.
Let us see this in brief:
Let us see how to create partitioned tables:
For this a special clause called as the PARTITION BY clause is used along with CREATE TABLE. This PARTITION BY clause is followed by the type of partition. There are four types of partition namely:
Having got a idea about the partionion concept, now let us see what each of the types of partition refer to.
RANGE: This type f partioning scheme is sued when one like to partition rows falling within a certain range. For example say in a sales table if the sales are less than say $5000 then they can be paced in one partition.
LIST: This type of partition allows users to pce a list of value for each partition and the value or rows falling in that list will be placed in the corresponding partition.Say if in a school the students belonging to class 1,4,5 can be placed in one partition and say the list belonging to students of class 7,8,3 can be placed in diffrenet partition.
HASH: This type of partition is based on the value of the hash function that is supplied within parenthesis after the type of partition. This helps in maintaning even distribution among tables.
KEY: This has functionality same as HASH but here the has function need not be specified by the user but MYSQL creates its own hashing function and uses it.
For the HASH and KEY partitions the user must specify the number of partions required by them.
Let us see an example to understand the above concept:
CREATE TABLE Exforsys(
sno INT PRIMARY KEY,
sname VARCHAR(20),
order_date DATE,
remarks VARCHAR(100)
) ENGINE = MYISAM
PARTITION BY RANGE(sno) (
PARTITION s0 VALUES LESS THAN(5000),
PARTITION s1 VALUES LESS THAN(10000),
PARTITION s2 VALUES LESS THAN(15000),
PARTITION s3 VALUES LESS THAN(20000),
PARTITION s4 VALUES LESS THAN(30000)
);
In the above we can see that the PARTITION clause is placed in the CREATE statement after all the columns in the end.
Also in the above 5 partitions s0,s1,s2,s3,s4 are created. In this so will have value of sno less than 5000 , s1 will have value less than 10000 and greater than 5000 because the rowstill 5000 have been fetched in the previous partition.
What is Partitioning concept and to how to achieve this using MYSQL?
One of the important concepts in database management is Partitioning. Partitioning is the concept by which storage of tables are pre-organized to attain efficiency. That is in other words the rows present in a table are stored across different disks or fields to achieve optimization and performance improvement.
How to achieve this concept in MYSQL
Database statements generally get divided into three broad categories namely:
For having this partitioning concept introduced the syntax of Data Manipulation Statements like SELECT, UPDATE, INSERT, DELETE do not gets changed. But the data definition statements like CREATE TABLE and ALTER TABLE have some additions in the syntx to have the partitioning concept added to the tables.
Let us see this in brief:
Let us see how to create partitioned tables:
For this a special clause called as the PARTITION BY clause is used along with CREATE TABLE. This PARTITION BY clause is followed by the type of partition. There are four types of partition namely:
Having got a idea about the partionion concept, now let us see what each of the types of partition refer to.
RANGE: This type f partioning scheme is sued when one like to partition rows falling within a certain range. For example say in a sales table if the sales are less than say $5000 then they can be paced in one partition.
LIST: This type of partition allows users to pce a list of value for each partition and the value or rows falling in that list will be placed in the corresponding partition.Say if in a school the students belonging to class 1,4,5 can be placed in one partition and say the list belonging to students of class 7,8,3 can be placed in diffrenet partition.
HASH: This type of partition is based on the value of the hash function that is supplied within parenthesis after the type of partition. This helps in maintaning even distribution among tables.
KEY: This has functionality same as HASH but here the has function need not be specified by the user but MYSQL creates its own hashing function and uses it.
For the HASH and KEY partitions the user must specify the number of partions required by them.
Let us see an example to understand the above concept:
In the above we can see that the PARTITION clause is placed in the CREATE statement after all the columns in the end.
Also in the above 5 partitions s0,s1,s2,s3,s4 are created. In this so will have value of sno less than 5000 , s1 will have value less than 10000 and greater than 5000 because the rowstill 5000 have been fetched in the previous partition.
Questions by GeekAdmin answers by GeekAdmin
This Question is not yet answered!
Related Answered Questions
Related Open Questions