Everything You Need to Know About MySQL Partitions

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:

SELECT     PLUGIN_NAME as Name,     PLUGIN_VERSION as Version,     PLUGIN_STATUS as Status     FROM INFORMATION_SCHEMA.PLUGINS     WHERE PLUGIN_TYPE='STORAGE ENGINE';

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.

Partitioning Basics

Before going further, it’s important to note some general considerations related to partitioning:

• Optimized Datetime Functions: The TO_DAYS(), YEAR(), and TO_SECONDS() functions are optimized for use in partitions. You can also use date and time functions that return integer or NULL values, such as WEEKDAY(), DAYOFYEAR(), or MONTH().
• Partition Identification: Partitions are always numbered sequentially, automatically starting from 0 when created. Rows are inserted using the partition numbers to identify where each row goes. For instance, if you partition a table into four, then MySQL will use the partition numbers 0, 1, 2, and 3 to identify each partition.
• Naming Conventions: Partition names should follow the same MySQL naming conventions used for tables and databases. It’s worth noting that partition names are not case sensitive. If you try to name two partitions for the same table – i.e. “myVertabeloPart” and “myvertabelopart” – you would get this error message:

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  
• Directory: Partitions can be assigned to a specific directory during creation. You can do this using the DATA DIRECTORY and INDEX DIRECTORY options for the PARTITION clause of the CREATE TABLE statement.
• Engine: Partitions of the same table must use the same storage engine. If you partition a MyISAM table, all partitions will be MyISAM; the same applies to InnoDB.
• Index Partitioning: Partitioning applies to all the data and indexes in a table; you cannot partition only the data and not the indexes, or vice versa. You also cannot partition only a portion of the table.
• Foreign Keys: Partitioned InnoDB tables do not support foreign keys. The data integrity implications of this are very important. You cannot add a foreign key (pointing to another table) in the partitioned table. Conversely, if the table has a foreign key you will not be able to partition it. Also, a nonpartitioned table cannot have a foreign key column pointing to a partitioned table column.
• Partition Columns: The rule of thumb here is that all columns used in the partitioning expression must be part of every unique key in the partitioned table. This apparently simple statement imposes certain important limitations, which we will discuss next.

Note: MySQL documentation explains partition restrictions and limitations HERE.

Partition Columns Explained

As stated earlier, all columns used in the partition expression must be present in every unique key in the table, including the primary key (which is UNIQUE by definition). In other words, all unique keys in the table must use all the columns in the partitioning expression.

For instance, this …

CREATE TABLE userslogs (     id INT NOT NULL AUTO_INCREMENT,     username VARCHAR(20) NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL,     PRIMARY KEY(id),     UNIQUE KEY (username, created) ) PARTITION BY HASH( TO_DAYS(created) ) PARTITIONS 10;

… can’t be partitioned, as “created” is not part of every unique key in the table. In this case, the primary key does not include “created”, so the statement fails:

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

We could rewrite the CREATE like this (depending on your use case):

CREATE TABLE userslogs (     username VARCHAR(20) NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL,     PRIMARY KEY(username, created) ) PARTITION BY HASH( TO_DAYS(created) ) PARTITIONS 10;

If you create a table without unique keys (including primary keys), any column can be used in the partitioning expression that is compatible with the partitioning type.

Next, I’ll explain what kinds of partitions are available in MySQL as of version 5.7.

EDIT MODEL IN YOUR BROWSER

Partition Types

There are basically four partition types available: RANGE, LIST, HASH and KEY. I’ll explain each of these now. Later on we will see some practical examples of using RANGE, the partition type you will likely end up using most.

RANGE Partitioning

This type of partition assigns rows to partitions based on column values that fall within a stated range. The values should be contiguous, but they should not overlap each other. The VALUES LESS THAN operator will be used to define such ranges in order from lowest to highest (a requirement for this partition type). Also, the partition expression – in the following example, it is YEAR(created) – must yield an integer or NULL value.

As with the example above, we could partition the table like this:

CREATE TABLE userslogs (     username VARCHAR(20) NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL,     PRIMARY KEY(username, created) ) PARTITION BY RANGE( YEAR(created) )(     PARTITION from_2013_or_less VALUES LESS THAN (2014),     PARTITION from_2014 VALUES LESS THAN (2015),     PARTITION from_2015 VALUES LESS THAN (2016),     PARTITION from_2016_and_up VALUES LESS THAN MAXVALUE

Note the MAXVALUE expression I’ve used in this definition. This is not mandatory, but it works as a catch-all for logs equal to or greater than the max value (year 2016). If I didn’t do this, consider what would happen when there is an INSERT with a YEAR value of 2020. It would fail, as there wouldn’t be any partition definition for that value.

As time goes by and 2017 kicks in, you would probably (depending on your use case) find it useful to generate a new partition for this table. You do this by altering the partition definition to generate a new partition for rows from 2016 (PARTITION from_2015 VALUES LESS THAN (2017)) as well as records from 2017 and later (PARTITION from_2017_and_up VALUES LESS THAN MAXVALUE). You could use the same logic for other years.

In relation to NULL values, it’s important to note that such rows will fall in the lower partition. In the former example, a YEAR with a NULL would evaluate as LESS THAN (2014), therefore it would end up inside the partition from_2013_or_less.

RANGE partitioning works best for:

• Deleting Old Data: In the above example, if logs from 2013 need to be deleted, you can simply use ALTER TABLE userslogs DROP PARTITION from_2013_or_less; to delete all rows. This process will take almost no time, whereas running DELETE FROM userslogs WHERE YEAR(created) <= 2013; could take minutes if there are lots of rows to delete.
• Series Data: Working with a range of data expressions comes naturally when you’re dealing with date or time data (as in the example) or other types of “series” data.
• Frequent Queries on the Partition Expression Column: If you frequently perform queries directly involving the column used in the partition expression (where the engine can determine which partition(s) it needs to scan based directly on the WHERE clause), RANGE is quite efficient. We will discuss this in greater detail in the Partition Pruning section.

An alternative to RANGE is RANGE COLUMNS, which allows the expression to include more than one column involving STRING, INT, DATE, and TIME type columns (but not functions). In such a case, the VALUES LESS THAN operator must include as many values as there are columns listed in the partition expression. For example:

CREATE TABLE rc1 (     a INT,     b INT ) PARTITION BY RANGE COLUMNS(a, b) (     PARTITION p0 VALUES LESS THAN (5, 12),     PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE) );

Values will be compared in tuples; for instance, these INSERTs...

INSERT INTO rc1 (a,b) VALUES (4,11); INSERT INTO rc1 (a,b) VALUES (5,11); INSERT INTO rc1 (a,b) VALUES (6,11); INSERT INTO rc1 (a,b) VALUES (4,12); INSERT INTO rc1 (a,b) VALUES (5,12); INSERT INTO rc1 (a,b) VALUES (6,12); INSERT INTO rc1 (a,b) VALUES (4,13); INSERT INTO rc1 (a,b) VALUES (5,13); INSERT INTO rc1 (a,b) VALUES (6,13);

… would be placed in the following way:

a	b	partition	comparison
4	11	p0	(4,11) < (5,12) = true
4	12	p0	(4,12) < (5,12) = true
4	13	p0	(4,13) < (5,12) = true
5	11	p0	(5,11) < (5,12) = true
5	12	p3	(5,12) < (5,12) = false
5	13	p3	(5,13) < (5,12) = false
6	11	p3	(6,11) < (5,12) = false
6	12	p3	(6,12) < (5,12) = false
6	13	p3	(6,13) < (5,12) = false

You can verify this for yourself using this code snippet:

SELECT *,'p0' FROM rc1 PARTITION (p0) UNION ALL SELECT *,'p3' FROM rc1 PARTITION (p3) ORDER BY a,b ASC;

LIST Partitioning

LIST partitioning is similar to RANGE, except that the partition is selected based on columns matching one of a set of discrete values. In this case, the VALUES IN statement will be used to define matching criteria. Let’s see an example:

CREATE TABLE serverlogs (     serverid INT NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL ) PARTITION BY LIST (serverid)(     PARTITION server_east VALUES IN(1,43,65,12,56,73),     PARTITION server_west VALUES IN(534,6422,196,956,22) );

Note that in LIST partitioning, there is no catch-all (like the MAXVALUE expression in RANGE). You must cover all possible elements in the criteria list to prevent an INSERTerror.

It’s worth noting that here NULL values are treated like any other value. In the above example, if serverid was NULLable and the expression evaluated to NULL for a given record, then that row would end up in the partition that accepts NULL as a possible value. Otherwise it would be rejected, as VALUES IN decides what value to put into which record.

LIST comes with an alternative – LIST COLUMNS. Like RANGE COLUMNS, this statement can include multiple columns and non-INT data types, such as STRING, DATE, and TIME. The general syntax would look like this:

CREATE TABLE lc (     a INT NULL,     b INT NULL ) PARTITION BY LIST COLUMNS(a,b) (     PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),     PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),     PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),     PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) );

Maintaining many columns with many values will usually get cumbersome, so you’ll most likely use LIST COLUMNS when you need to use many non-INT columns. This is shown in the following example:

CREATE TABLE serverlogs (     servername VARCHAR(20) NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL ) PARTITION BY LIST COLUMNS (servername)(     PARTITION server_east VALUES IN('northern_east','east_ny'),     PARTITION server_west VALUES IN('west_city','southern_ca') );

HASH Partitioning

In HASH partitioning, a partition is selected based on the value returned by a user-defined expression. This expression operates on column values in rows that will be inserted into the table. A HASH partition expression can consist of any valid MySQL expression that yields a nonnegative integer value. HASH is used mainly to evenly distribute data among the number of partitions the user has chosen.

For RANGE and LIST, one must define the partitions where the data will be stored; HASHdoes this automatically, based on the expression or INT value of the selected column. Let’s see how it works:

CREATE TABLE serverlogs2 (     serverid INT NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL ) PARTITION BY HASH (serverid) PARTITIONS 10;

Note: If you don’t include the PARTITIONS keyword, the default number of partitions is “1”, and that’s how many you’ll get.

Here are some recommendations about using HASH partitions:

• PARTITION BY HASH (expression) must return a non-constant, non-random integer value. (In other words, the returned value should be varying but deterministic.) The expressions TO_DAYS or YEAR are good to use with functions – e.g. based on the “created” column in our example.
• The use of hashing expressions involving multiple columns is not particularly recommended; determining which of such expressions is suitable can be quite difficult and time-consuming.
• A user-supplied expression is evaluated each time a record is inserted or updated. It may also be evaluated when records are deleted (depending on the circumstances).
• MySQL determines which partition stores values by using the formula N = MOD(expr, num) where N is the resulting partition number, expr is the expression and num is the number of partitions defined in the PARTITIONS keyword. An alternative to this is LINEAR HASH.

LINEAR HASH

Instead of using the modulo described above, when MySQL uses LINEAR HASH a powers-of-two algorithm is employed to calculate the partition where the data is to be stored. Syntactically, LINEAR HASH is exactly the same as HASH, except for the addition of the word LINEAR:

CREATE TABLE serverlogs2 (     serverid INT NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL ) PARTITION BY LINEAR HASH (serverid) PARTITIONS 10;

It is advisable to use linear hashing when dealing with tables containing extremely large amounts (i.e. terabytes) of data. In such cases, adding, dropping, merging, and splitting partitions is much faster. However, this comes at a price: the distribution of data is not as even as with regular hashing. It is best to choose a number of partitions that is a power of 2 (2,4,8,16, etc.), otherwise the middle partitions will tend to be twice the size of the outer ones. This is due to the statistical distribution of the linear hash.

KEY Partitioning

This is very similar to HASH partitioning, but the hashing function is supplied by MySQL. A KEY partition can specify zero or many columns, which can contain non-integer values. An integer result will be returned regardless of the column data type. The following example will clarify this:

CREATE TABLE serverlogs4 (     serverid INT NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL,     UNIQUE KEY (serverid) ) PARTITION BY KEY() PARTITIONS 10;

Here KEY() has been used without explicitly stating the partitioning column. MySQL will automatically use the primary key or a unique key as the partitioning column. If no unique keys are available, the statement will fail.

As stated before, we can define several columns as part of the KEY keyword. This is demonstrated in the following example:

CREATE TABLE serverlogs5 (     serverid INT NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL,     label VARCHAR(10) NOT NULL ) PARTITION BY KEY(serverid, label, created) PARTITIONS 10;

KEY comes with a LINEAR keyword option that has the exact same impact as the LINEARkeyword in HASH: the partitions are derived using a power-of-two algorithm. See how it works in the following example:

CREATE TABLE serverlogs6 (     serverid INT NOT NULL,     logdata BLOB NOT NULL,     created DATETIME NOT NULL ) PARTITION BY LINEAR KEY(serverid) PARTITIONS 10;

Partition Pruning

The concept behind partition pruning is very simple: don’t search partitions where there is nothing to be found. Take the following example:

CREATE TABLE samplelogs (     logdata BLOB NOT NULL,     created DATETIME NOT NULL ) PARTITION BY RANGE COLUMNS (created)(     PARTITION oldlogs VALUES LESS THAN ('2015-01-01'),     PARTITION currentlogs VALUES LESS THAN (MAXVALUE) );

If we perform this statement…

SELECT * FROM samplelogs WHERE created > '2016-01-01'

... the optimizer will kick in and leave unnecessary partitions out of the search. Thus, the query is more efficient. You can see this by running an EXPLAIN statement:

EXPLAIN PARTITIONS SELECT * FROM samplelogs WHERE created > '2016-01-01'

The output shows that only the “currentlogs” partition is being used.

The optimizer automatically “prunes” only when the WHERE condition can be simplified as one of the following two cases:

• partition_column = constant
• partition_column IN (constant1, constant2, ..., constantN)

You can also explicitly select the partition like this:

SELECT * FROM samplelogs PARTITION (currentlogs) WHERE created > '2016-01-01';

When partition names are generated by MySQL (as in HASH and KEY partitions) the name pattern is p0, p1,..., pN-1 (where N is the number of partitions). Partition selection is supported for most operations like DELETE, UPDATE, INSERT, JOIN, etc.

A Typical Use Case: Time Series Data

Partitions are commonly used when dealing with a series of time data that contains a lot of records. These could be logs or records of phone calls, invoices, samples, etc. Most of the time, you’ll be reading fresh data, so there will be lots of old rows that need deleting once they get “stale”.

To illustrate how to deal with this problem, I’ll present a user case scenario using 1.8 million records from a weather station network. I’ll perform some common operations on the “measures” table as-is; I will also partition a copy of this table by RANGE and see if there is a difference.

Millions of Records

We will use the following table:

CREATE TABLE `measures` (   `measure_timestamp` datetime NOT NULL,   `station_name` varchar(255) DEFAULT NULL,   `wind_mtsperhour` int(11) NOT NULL,   `windgust_mtsperhour` int(11) NOT NULL,   `windangle` int(3) NOT NULL,   `rain_mm` decimal(5,2),   `temperature_dht11` int(5),   `humidity_dht11` int(5),   `barometric_pressure` decimal(10,2) NOT NULL,   `barometric_temperature` decimal(10,0) NOT NULL,   `lux` decimal(7,2),   `is_plugged` tinyint(1),   `battery_level` int(3),   KEY `measure_timestamp` (`measure_timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next, we’ll make a copy of the former, adding RANGE partitions:

CREATE TABLE `partitioned_measures` (   `measure_timestamp` datetime NOT NULL,   `station_name` varchar(255) DEFAULT NULL,   `wind_mtsperhour` int(11) NOT NULL,   `windgust_mtsperhour` int(11) NOT NULL,   `windangle` int(3) NOT NULL,   `rain_mm` decimal(5,2),   `temperature_dht11` int(5),   `humidity_dht11` int(5),   `barometric_pressure` decimal(10,2) NOT NULL,   `barometric_temperature` decimal(10,0) NOT NULL,   `lux` decimal(7,2),   `is_plugged` tinyint(1),   `battery_level` int(3),   KEY `measure_timestamp` (`measure_timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `vertabelo`.`partitioned_measures`  PARTITION BY RANGE (YEAR(measure_timestamp)) (     PARTITION to_delete_logs VALUES LESS THAN (2015),     PARTITION prev_year_logs VALUES LESS THAN (2016),     PARTITION current_logs VALUES LESS THAN (MAXVALUE)  ) ;

The SELECT Queries Benchmark

Let’s run a pair of common queries on both tables and see how they compare. (I will use the SQL- NO_CACHE keyword so we can see the impact of the query without engine caching):

SELECT SQL_NO_CACHE     COUNT(*) FROM     vertabelo.measures WHERE     measure_timestamp >= '2016-01-01'         AND DAYOFWEEK(measure_timestamp) = 1;       SELECT SQL_NO_CACHE     COUNT(*) FROM     vertabelo.partitioned_measures WHERE     measure_timestamp >= '2016-01-01'         AND DAYOFWEEK(measure_timestamp) = 1;

It takes around half a second on my local machine to return the count of 112,153 rows for both queries (partitioned and nonpartitioned). Notice that both tables have an index on measure_timestamp; we’d expect this index to have a huge impact on the queries’ efficiency.

If I remove the indexes in both tables, I can see any impact the partitioning has on the SELECT:

ALTER TABLE `vertabelo`.`measures` DROP INDEX `measure_timestamp` ;   ALTER TABLE `vertabelo`.`partitioned_measures` DROP INDEX `measure_timestamp` ;

Conclusion: Running the SELECT on the nonpartitioned table takes about twice the timeas the same query on the partitioned table.

The Big Delete Benchmark

I will try a massive deletion of old data and see how long it takes. Before I do that, let’s add an index back on the measure_timestamp column:

ALTER TABLE `vertabelo`.`measures` ADD INDEX `index1` (`measure_timestamp` ASC);

ALTER TABLE `vertabelo`.`partitioned_measures` ADD INDEX `index1` (`measure_timestamp` ASC);

We have it as it originally was, with indexed data.

Now let’s do a big delete on the nonpartitioned table:

DELETE FROM vertabelo.measures WHERE  measure_timestamp < '2015-01-01';

It takes about 0.7 seconds to delete 85,314 rows. What if I run a DROP PARTITION to achieve the same result in the partitioned table?

ALTER TABLE vertabelo.partitioned_measures DROP PARTITION to_delete_logs ;

It drops it in less than 0.06 seconds. Let’s try deleting all data older than 2016...

DELETE FROM vertabelo.measures WHERE  measure_timestamp < '2016-01-01';

… and then dropping the equivalent data from the partitioned table:

ALTER TABLE vertabelo.partitioned_measures DROP PARTITION prev_year_logs ;

Look at the output for each:

DELETE FROM vertabelo.measures WHERE measure_timestamp < '2016-01-01'	988644 row(s) affected	56.125 sec
ALTER TABLE vertabelo.partitioned_measures DROP PARTITION prev_year_logs	0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0	0.047 sec

As you can see the process of deleting data is way faster when you have partitions.

Conclusion

From personal experience, partitioning is the last part of any optimization process I’d perform. I’d do it only after exhausting other alternatives, like reworking slow queries. In general, partitioning makes the most sense when you’re dealing with millions of records. In this case, I have found RANGE to be the most useful. The best-fitting use case for RANGE is massively deleting old time-series data.

Before you set up any partitions, remember that they impose several limitations – the way that unique and primary keys are generated; the fact that foreign keys are not allowed; the lack of support for full-text indexes, etc.