What is MySQL RANGE Partitioning

Partitioning is a process in which large tables of the database are divided into separate tables but treated as a single table. We can make partitions in the database using MySQL and place the data according to these partitions. In the MySQL database, the only horizontal partition is supported and it is of six types. In this post, RANGE partitioning will be discussed in detail and explained with the help of examples so the readers can understand it.

What is Range Partitioning

Range partitioning in MySQL allows you to partition the database by defining a specific range; the entries coming within the defined range would be considered in the partition. To understand more precisely the Range partitioning and its use; let us consider this example, create a table of students of the school bus and partitioned them on the basis of their age using the command:

CREATE TABLE school_bus_data (std_id INT NOT NULL, std_name VARCHAR(50) NOT NULL,std_age INT NOT NULL) PARTITION BY RANGE(std_age) (PARTITION p0 VALUES LESS THAN(10),PARTITION p1 VALUES LESS THAN(15),PARTITION p2 VALUES LESS THAN(20));

In the above-executed command, we have created three partitions; p0,p1,p2:

• Students of age less than 10 are placed in partition p0
• Students of age less than 15 but greater than 10 are placed in partition p1
• Students between 15-20 are placed in partition p2

To insert the data in this table, use the command:

INSERT INTO school_bus_data VALUES (1, ‘Paul’, 9), (2,’Sophia’,5),(3,’Elena’,14),(4,’John’,18),(5,’Alexander’,11);

To display the school_bus_data table:

SELECT * FROM school_bus_data;

To display in the form of partition of the table using the command:

SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'Linuxhint' AND TABLE_NAME = 'school_bus_data';

In the above output, we can see that the two table rows are placed in partition p0 and p1, and one row is placed in p2 on the basis of the std_age we defined. Moreover, the table also displayed the average row length and data length of each partition.

Conclusion

RANGE partitioning helps you to manage the data more easily as you can sort the data according to a specific range and then utilize it when it is needed. MySQL is the popular RDMS; it allows the feature of the partitions which speed up the execution of the queries in managing the data of the databases. The data is divided into partitions on the basis of some parameters. In this post, we discussed one of these parameters of partition; Range also discussed an example for the better understanding of readers.

from https://ift.tt/3F4QNit