Trending October 2023 # Guide To Types &Amp; Techniques Of Partitions In Mysql # Suggested November 2023 # Top 18 Popular

You are reading the article Guide To Types &Amp; Techniques Of Partitions In Mysql updated in October 2023 on the website Vibergotobrazil.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Guide To Types &Amp; Techniques Of Partitions In Mysql

Introduction to MySQL Partition

Hadoop, Data Science, Statistics & others

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

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 Techniques of Partitioning

Given below are the techniques of partitioning:

1. Range Partitioning

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

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