Horizontal and Vertical PartitioningThere are two types of database partitions—vertical and horizontal. Show Vertical PartitioningVertical partitioning relies upon initially creating tables with fewer columns and then using additional tables to store leftover columns. Since rows are split according to their columns, vertical partitioning is also known as row splitting (opposite horizontal
partitioning). As of right now, MySQL does not support vertical partitioning in its database. Horizontal PartitioningThe partitioning logic divides the rows into multiple tables. The number of columns remains constant throughout partitions, while the number of rows can vary. My SQL currently supports horizontal partitioning. In this post, we’ll look at three different kinds of horizontal partitions in MySQL. Range PartitioningWhen implementing range partitioning, if a column value falls within the specified range for a particular partition, the row is then added to that partition. Implementing Range Partitioning
2. (Optional) Populate the table with sample data. In this case, download this dataset. Then, import the CSV data into a table. 3. Create the range partitions using the
Another variation of the range partitioning is the
List PartitioningIn list partitioning, rows are grouped on the premise that their value in the column used for partitioning is similar to a value in the list (set of discrete values) defined. To put things in perspective, when you create the partitioning list(s), MySQL checks, “does a column in this row have a similar value with values in this list ?”. If the value is the same, MySQL adds that row to the partition for that value in your list. Implementing List Partitioning
Hash PartitioningWhen defining the previous partitions, you had to state which column value, range, or column values fell into a particular partition. With hash partitioning, you must specify the number of partitions you want for the column (or expression). MySQL uses For the first row, the partition it belongs to based on the Implementing Range PartitioningYou can implement range partitioning in MySQL using the same
ConclusionIn general, partitions speed up your searches. While this is correct, the effect of partitions is not readily apparent in smaller tables. So, if your queries are executing slowly and your database table does not include millions of rows, you should consider other optimization approaches before splitting your tables. Further Reading
|