What is MySQL partitioning? What kind of partition types are there? How do you know if this is something your database engine supports? In this article, we tell you what you need to know about partitioning in MySQL.

MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data (a.k.a. partitions) across the file system based on a set of user-defined rules (a.k.a. the “partitioning function”). In this way, if the queries you perform access only a fraction of table data and the partitioning function is properly set, there will be less to scan and queries will be faster.

It is important to note that partitioning makes the most sense when dealing with large data sets. If you have fewer than a million rows or only thousands of records, partitioning will not make a difference.

Horizontal vs. Vertical Partitioning

Horizontal partitioning means that all rows matching the partitioning function will be assigned to different physical partitions. Vertical partitioning allows different table columns to be split into different physical partitions. Currently, MySQL supports horizontal partitioning but not vertical. The engine’s documentation clearly states it won’t support vertical partitions any time soon: ”There are no plans at this time to introduce vertical partitioning into MySQL.”

Vertical partitioning is about splitting up columns

Horizontal partitioning is about splitting up rows

Key Benefits of Partitioning

Some of the advantages of using partitions are:

• Storage: It is possible to store more data in one table than can be held on a single disk or file system partition.
• Deletion: Dropping a useless partition is almost instantaneous, but a classical DELETE query run in a very large table could take minutes.
• Partition Pruning: This is the ability to exclude non-matching partitions and their data from a search; it makes querying faster. Also, MySQL 5.7 supports explicit partition selection in queries, which greatly increases the search speed. (Obviously, this only works if you know in advance which partitions you want to use.) This also applies for DELETE, INSERT, REPLACE, and UPDATE statements as well as LOAD DATAand LOAD XML.

How to Check If Your Engine Supports Partitioning

By default, community binaries include partitioning support. You can check if it’s being supported in your current instance by running the SHOW PLUGINS statement. The output should display something like the following row:

| Name       | Status   | Type           | Library | License |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

Another alternative is to check INFORMATION_SCHEMA.PLUGINS:

You should get something like this:

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Both outputs note that the plugin you’re looking for is named “partition” and that it is set to ACTIVE. If you don’t see the partition listed by either of these methods, your MySQL version likely was installed without partitioning support. In this case, another MySQL compilation must be performed with the –DWITH_PARTITION_STORAGE_ENGINE option. The “INSTALLING MYSQL FROM SOURCE” documentation will show you how to do this.

About Author

Reetesh Gupta

administrator

See author's posts