All You Need to Know About MySQL Partitions

MySQL partitioning is about changing the way the database engine physically stores data–ideally optimizing it. You can distribute portions of table data (a.k.a. partitions) throughout the file system based on a set of user-defined rules (a.k.a. "partitioning function"). In this way, if only a fraction of table data is accessed by the queries and the partitioning function is set properly, there will be less scanning and it will be faster to scan queries.

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

Horizontal and Vertical Partitioning

Horizontal partitioning means that different physical partitions are assigned to all rows that match the partitioning function. Vertical partitioning allows the splitting into different physical partitions of different table columns. MySQL currently supports horizontal, but not vertical, partitioning. The documentation of the engine clearly states that vertical partitions will not be supported in the near future: "There are no plans to introduce vertical partitioning into MySQL at this time."

Benefits of partitioning

Storage: More data can be stored in one table than can be stored on a single partition of the disk or file system.

Deletion: It's almost instantaneous to drop a useless partition, but a classic DELETE query running in a very big table might take minutes.

Partition Pruning: This is the ability to exclude from a search non-matching partitions and their data; it makes it faster to query. MySQL 5.7 also supports explicit selection of partitions in queries, which significantly increases the speed of search. This also applies to DELETE, INSERT, REPLACE and UPDATE statements as well as LOAD DATA and LOAD XML statements. (Obviously, this only works if you know in advance which partitions you want to use.)

How to check if the engine supports partitioning

Community binaries by default include support for partitioning. By running the SHOW PLUGINS statement, you can check whether it is supported in your current instance. Both outputs note that the plugin you are searching for is called "partition" and is set to ACTIVE. If you do not see the partition listed by either of these methods, your version of MySQL will probably have been installed without support for partitioning. In this case, the-DWITH PARTITION STORAGE ENGINE option must be used to perform another MySQL compilation. The documentation on "Installing MySQL from Source" will show you how to do this.

Basics of partitioning

Optimized date-time functions: For partition use, the functions TO DAYS(), YEAR(), and TO SECONDS() are optimized. You can also use date and time functions returning integer values or NULL values like WEEKDAY(), DAYOFYEAR(), or MONTH().

Identification of partitions: Partitions are always numbered sequentially, starting when created automatically from 0. Using partition numbers, rows are inserted to identify where each row is going. For example, if you partition a table into four, the partition numbers0, 1, 2, and 3 will be used by MySQL to identify each partition.

Naming conventions: Partition names should follow the same tables and database naming conventions for MySQL. It is worth noting that names of partitions are not sensitive to cases.

Directory: Partitions can be assigned during creation to a specific directory. You can do this with the CREATE TABLE statement's DATA DIRECTORY and INDEX DIRECTORY options.

Engine: The same table partitions must use the same storage engine. All partitions will be MyISAM if you partition a MyISAM table; the same applies to InnoDB.

Index Partitioning: Partitioning applies to all data and indexes in a table; it is not possible to partition data only and not indexes, or vice versa. You can't partition just a portion of the table as well.

Foreign keys: Partitioned tables of InnoDB do not support foreign keys. The implications of this for data integrity are very important. In the partitioned table, you can't add a foreign key (pointing to another table). Conversely, you won't be able to partition it if the table has a foreign key. A non-partitioned table cannot also have a foreign key column pointing to a column of a partitioned table.

Partition Columns: The rule of thumb here is that in the partitioned table, all columns used in the partitioning expression must be part of every single key. This seemingly simple statement imposes some important limitations that we will discuss next.

Eric Vanier is a database expert who, with his team of mysql consulting professionals, has helped numerous Fortune 500 companies maintain their database. The present scenario of database management is very cluttered and requires both skill and expertise to champion. Thanks to him, companies are now dependent on a smooth-running and have experienced mysql performance improvement.