A walkthrough on partitions in MySQL

Shripati Bhat
|
min read
A walkthrough on partitions in MySQL

If you have chosen data to be stored in MySQL and if your data size is growing rapidly or if you are looking for faster access based on your predicates, then partitioning could be one solution that you want to understand better. In Engati, we deal with millions of users and conversations between users across 14 different channels. Querying this data for a particular customer’s user and expecting result in a few milliseconds is a business need for our flows. That’s where we adopt partitioning of data.


Imagine walking into a Library where there are only 100 books, here you won’t need a lot of organizing the books. Although it will still take you some time to find books there, but you will still be able to manage. Especially over time you will get comfortable to where you kept a particular book. But imagine that you were in a presidential library or one such huge library, you surely don’t want to spend hourly looking for one book. Here obviously racks are arranged alphabetically so that you can find the book that you need easily. 


Very similarly datastores build caches to reply to queries when the dataset is small. But when the data in your datastore is in the order of hundreds of thousands or millions or billions of records, then datastores provide an option for you to create physical and/or logical segregations to find the data easily. MySQL only supports logical segregation of data called the partitions.


Just like books in a Library or Book Store can be arranged alphabetically by the name of the book or the name of the author or the year they were published in, MySQL supports various types of partitioning strategies which can be used based on the business objective.


There are many types of partitions that MySQL supports. Some of the most commonly understandable ones are:

  1. Hash Partitions – As the name suggests, here the data in the MySQL table is partitioned based on a hashing function of the column of your interest. This ensures more uniform and equal sized partitions and all you need to do is specify the number of partitions you need (which people generally people to be a power of 2). For example, partitioning by a name column in a User table

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL
)
PARTITION BY HASH (name) PARTITIONS 16;
            

  1. Range Partitions – Again the name suggests here the data in the MySQL table is partitioned based on a (non-overlapping) range of data stored in a column. For example, if you want to partition based on the value of a column age in a User table.

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL
)
PARTITION BY RANGE (age) (
        PARTITION p0 VALUES LESS THAN (20),
        PARTITION p1 VALUES LESS THAN (40),
        PARTITION p2 VALUES LESS THAN (60),
        PARTITION p3 VALUES LESS THAN (80),
        PARTITION p4 VALUES LESS THAN MAXVALUE 
);


  1. List Partitioning – In this case, the table definition needs to provide a list of values for a column that would go in any partition. This partition type can be chosen when there is list of different values of data going into a column like ENUM. A point to note here is that a list partition fails the insert operation if the value of the column is not defined in any of the partition definition (unlike range partition). For instance, if you want to partition based on continent column in a User table.

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL,

        continent VARCHAR(20) NOT NULL
)
PARTITION BY LIST COLUMNS (continent) (
        PARTITION p0 VALUES IN (“ASIA”),
        PARTITION p1 VALUES IN (“AFRICA”),
        PARTITION p2 VALUES IN (“EUROPE”, “ANTARTICA”),
        PARTITION p3 VALUES IN (“NORTH AMERICA”, “SOUTH AMERICA”, “OCEANIA”)
);


As you can see, MySQL supports partitioning data of a column of data type integer or varchar with list partitions. The same is supported in range partitions too by mapping a non-integer column value to an integer value. Hash partition has another variant called Linear Hash Partition which uses a power of 2 algorithm to decide which partition the data gets inserted into vs the regular modulus of a hash function value.


Partitioning data can come handy if the size of your table grows in millions and the data lookup is slow despite adding different types of indexes on a table. In a follow-up blog we will talk about partitioning existing tables in MySQL, so stay tuned.


Tags
No items found.
About Engati

Engati powers 45,000+ chatbot & live chat solutions in 50+ languages across the world.

We aim to empower you to create the best customer experiences you could imagine. 

So, are you ready to create unbelievably smooth experiences?

Check us out!