MySQL PARTITIONING

From a previous post we talked about basic MySQL queries. From this post I’m going to introduce you much more advanced technique in MySQL known as partitioning. Let’s see what is partitioning. Using partition we can separate a single table into multiple parts. This will create only a single table with multiple sub parts. For example, first image shows a table without partitioning and the second image shows a table with partitioning.

So as you can see, partitioning will divide a single table into multiple parts, but still it’s a single table. So what are the advantages of using partitioning.

1. Faster single insertions.
2. Faster searching.
3. Split data across different paths.
4. Store historical data efficiency.
5. Can add a check constraint to a table.
6. To add control to Archive tables.
7. To differentiate roles on master and slave.

Though I said partitioning will reduce the search time, how to believe it? Let’s check it. First of all we have to check whether our mysql version supports partitioning or not.

If partition shows ACTIVE, then it’s ok. Then I create a table with partitioning and add some data to it. Next I create another table with same structure and but without partitioning. Later I add same data to both tables.

Table with partitioning :

Table without partitioning :

Now let’s retrieve data from both the tables and compare the time.

Table with partitioning :It took 0.00 seconds to complete

Table without partitioning : It took 0.01 seconds to complete

So there is a delay in the table without the partitioning.

There are 4 types of partitioning techniques in use,

1. RANGE
2. LIST
3. KEY
4. HASH

Also there are few types of engines as well,

Those two tables show the storage capacity and the query time of each engines. Now let’s see how to write queries using above mentioned 4 different types of techniques.

RANGE

• Let’s create a new table.

• The value to use in partitioning must be a integer. MAXVALUE is optional as well. Also the partitions should be in ascending order.
• Now let’s insert some data to it.

• Now let’s see how to add a new partition.
• I’m creating a new table. It contains 2 partitions.

• Now I’m going to add one more partition to it.

• using explain partitions, we can get information about the partitioning of a table. It shows newly added partition as well.

• In range, when we add a new partition it should be added to the last. Adding partitions anywhere else is not possible.

• Now let’s see how to remove a partition.

• It has removed the partition successfully.

LIST

• Now let’s see how to work with list.
• I’m creating a new table with list technique to partitioning.

• Then I’m inserting some data to it.

• Let’s retrieve data.

• updating and deleting partitions work the same way how we did it with range.

KEY

• Now let’s see the key partitioning.
• I’m creating a new table.

• Let’s add some data to it.

• Now try to retrieve data from the table.

• Now let’s see how to add a new partition to the same table.

• It has successfully added 2 new partitions to the table.

• Now I want to remove some partitions from the table.

• It has successfully removed 5 partitions from the table.

HASH

• The final technique hash.
• I’m creating another table with hashing.

• Let’s add some data to the table.

• Let’s retrieve data from it.

• Updating and removing is happening the same way how we did it with keys.

That’s all about partitioning in MySQL. If you encountered any error while creating the tables, you can check it.

If is there any type of error, it will be shown in the Msg_text column. If there is any error, we can repair it as well.

Finally if you want to get data from a specific partition, you can do it this way.

Hope now you have a clear idea about MySQL partitioning. Thank You!