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 MysqlIntroduction 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";
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.
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;
We hope that this EDUCBA information on “MySQL Partition” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
You're reading Guide To Types &Amp; Techniques Of Partitions In Mysql
Update the detailed information about Guide To Types &Amp; Techniques Of Partitions In Mysql on the Vibergotobrazil.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!