Updated May 30, 2023
Introduction to MySQL Partition
The following article provides an outline for MySQL Partition. Partitions are the dividers or separators that can be created in the database tables that store many records. Partitioning leads to scalability and an increase in the performance of the database access and retrieval of data with very few additional resources. Implementing partitions in your tables aids in faster data retrieval when you create appropriate queries and partitions.
You must always include the column or parameter on which the partition is created in the table in the WHERE clause of the INSERT, UPDATE, DELETE, and SELECT queries being constructed for that table. If not done so, it will lead to scanning whole table records instead of a single partition where that record will belong, which will be very time and resource-consuming.
Checking Support for Partitions in MySQL
We can check whether MySQL supports the partitions by checking the contents of the plugins table located inside the information_schema schema using the following query.
Code:
SELECT PLUGIN_STATUS,PLUGIN_NAME,PLUGIN_VERSION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE ENGINE' and PLUGIN_NAME = "partition";Output:
It gives the following output if your MySQL partition support is active:
Alternatively, you can fire the following command to check whether the partition plugin is active and supported by your version of MySQL.
Code:
SHOW PLUGINS;Output:
It gives the following output that will contain a plugin named partition and its status along with all other plugins of the database:
Types of MySQL Partition
Partitions can be broadly classified into two types:
1. Horizontal Partitioning
In horizontal partitioning, the data is split and partitioned by dividing the number of rows of the table.
For example, suppose a table consists of 150000 rows; then we can create any number of partitions, say ten, on that table, so each partition will contain 15000 rows. Horizontal partitioning reduces the number of rows to be managed while performing any database operation on that table. Horizontal partitioning is further classified depending on the type of key used for partitioning.
2. Vertical Partitioning
Vertical partitioning deals with the separation of columns instead of rows. Hence the resulting partitions will contain the same number of rows as in the original table, but the number of columns will be reduced. This works similarly to normalization, but vertical partitioning implements certain concepts that are even more advanced than normalization.
Techniques of Partitioning
Given below are the techniques of partitioning:
1. Range Partitioning
In this type of partition, we create the partitions based on the range of the values. This range needs to be consecutive, and at the exact time, none of the ranges should overlap the other one. All the records whose column value falls into the particular range value will be added to that partition. Creating the partitions based on range VALUES LESS THAN clause is used to define the section ranges while creating.
For example, let us create a table to store the country’s cities and create five partitions based on pin codes.
Code:
CREATE TABLE cities (
id INT NOT NULL,
name VARCHAR(30),
population VARCHAR(30),
pincode INT NOT NULL)
PARTITION BY RANGE (pincode) (
PARTITION p0 VALUES LESS THAN (215000),
PARTITION p1 VALUES LESS THAN (415000),
PARTITION p2 VALUES LESS THAN (615000),
PARTITION p3 VALUES LESS THAN (815000),
PARTITION p4 VALUES LESS THAN (999999));Output:
2. List Partitioning
List partitioning is quite similar in working to range partitioning. The only difference between both partitioning techniques is that in list partitioning, you need to mention the set of values in the comma-separated format for each partition instead of defining the whole range of values. Please note that the values used for partitioning must be discrete. PARTITION BY LIST (expression) clause says the expression can be any column with an integer datatype or any other expression on columns that will result in an integer value. Further, VALUES IN (list of values) is used to specify the set of discrete integer values that will fall into that partition.
For example, we will create a table named writers containing columns name, id, rate, and joining_date and four partitions based on rates. Consider that the fixed rate values are 500, 550, 600, 650,….1000. here, we will create four partitions depending on the rate values.
Code:
CREATE TABLE 'writers' (
'id' int(11) NOT NULL,
'name' varchar(10) COLLATE latin1_danish_ci NOT NULL,
'rate' integer DEFAULT NULL,
'joining_date' date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci
PARTITION BY LIST(rate) (
PARTITION low VALUES IN (500,550),
PARTITION medium VALUES IN (600,650,700),
PARTITION high VALUES IN (750,800,850),
PARTITION best VALUES IN (900,950,1000)
);Output:
3. Hash Partitioning
In hash partitioning, we do not need to specify the values that will be considered for the partitioning and which value will fall into which partition is decided by MySQL itself. We must determine the expression on which we will perform the hashing and the desired number of partitions to create. We can mention that we want to perform a hash partition by using PARTITION BY HASH (expression), where the expression should always return an integer value. Use PARTITIONS to mention the number of partitions to create.
For example, we will create an articles table and four partitions on the technology_id column using the hash partitioning technique.
Code:
CREATE TABLE articles (
id INT NOT NULL,
name VARCHAR(30),
status VARCHAR(30),
submit_date DATE NOT NULL DEFAULT '1970-01-01',
technology_id INT
)
PARTITION BY HASH(technology_id)
PARTITIONS 4;Output:
4. Key Partitioning
This technique resembles hash partitioning, with the distinction that MySQL performs the internal hashing of the columns, eliminating the need for explicitly specifying the hash expression. Instead, mention the PARTITION BY KEY(). These columns of keys don’t need to be of integer type.
Using the key partitioning technique, we create two partitions for the table “testers” based on their key, as illustrated in the following example.
Code:
CREATE TABLE testers (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;Output:
Conclusion
We can implement partitioning in the database when too much data resides in the tables. You can use various techniques for partitioning. Which one to choose depends upon the use case and requirement. Partition leads to excellent performance and scalability in the MySQL database.
Recommended Articles
We hope that this EDUCBA information on “MySQL Partition” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
