<script type="application/ld+json">
{
 "@context": "https://schema.org",
 "@type": "BlogPosting",
 "mainEntityOfPage": {
   "@type": "WebPage",
   "@id": "https://www.engati.com/blog/types-of-sql-partitions"
 },
 "headline": "A walkthrough on partitions in MySQL",
 "image": "https://global-uploads.webflow.com/5ef788f07804fb7d78a4127a/60926776b8aa168668a06416_A-walkthrough-on-partitions-in-MySQL-p-1600.jpeg",  

"articleSection" : "Types of MySQL partitions",  

"articleBody" : [ "Hash Partitions", "Range Partitions", "List Partitions" ],
 "author": {
   "@type": "Person",
   "name": "Shripati Bhat"
 },  
 "publisher": {
   "@type": "Organization",
   "name": "Engati",
   "logo": {
     "@type": "ImageObject",
     "url": "https://global-uploads.webflow.com/5ef788f07804fb9e0aa41273/60950e66372af76cf098f036_engati%20logo_color.svg"
   }
 },
 "datePublished": "2021-03-30",
 "dateModified": "2021-05-31"
}
</script>

Tech Corner

A walkthrough on partitions in MySQL

Shripati Bhat
.
Mar 30
.
2-3 mins

Table of contents

Key takeawaysCollaboration platforms are essential to the new way of workingEmployees prefer engati over emailEmployees play a growing part in software purchasing decisionsThe future of work is collaborativeMethodology

If you have chosen data to be stored in MySQL and if your data size is snowballing 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 to spend too much time organizing the books. Although it will still take you some time to find books there, you will still manage. Over time, you will remember where you kept a particular book. But imagine that you were in an enormous library, you surely don’t want to spend hours looking for one book. Here 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.

Types of MySQL partitions

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;
            

2

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 
);


3

List Partitions

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 a 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”, “ANTARCTICA”),
        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 algorithms to decide which partition the data gets inserted into v/s the regular modulus of a hash function value.

Partitioning data can come in handy if your table’s size 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

Share
Share

Shripati Bhat

Shripati is a Senior Technical Manager at Engati. He's a technical enthusiast, passionate about designing and building scalable software.

Shripati has a deeply ingrained customer-first ideology and is skilled in designing and developing Java/J2EE applications and BigData applications.

Andy is the Co-Founder and CIO of SwissCognitive - The Global AI Hub. He’s also the President of the Swiss IT Leadership Forum.

Andy is a digital enterprise leader and is transforming business strategies keeping the best interests of shareholders, customers, and employees in mind.

Follow him for your daily dose of AI news and thoughts on using AI to improve your business.

Catch our interview with Andy on AI in daily life

Continue Reading