MySQL, the world’s most popular open-source database management system, encompasses key attributes such as high performance, manageability, scalability, availability and ease of use. It has become the default database for building any new generation applications over any technology stack. Additionally, its capability to run on any platform has made it a reliable option to use because of its flexibility and control over the applications.

This reference card provides an overview of the MySQL database partitioning techniques and how these techniques lead to operational excellence.

Partitioning is a database design technique with details as follows

MySQL Optimizer typically contains the set of routines that decide what execution path the database server should take for executing the queries.

The two very basic database partitioning practices are as follows.

MySQL implements the database partitioning feature through user-defined partitioning.

To determine whether the MySQL server supports partitioning features or not, issues a SHOW VARIABLES statement at the mysql prompt, as show below.

mysql> SHOW VARIABLES LIKE '%partition%';
    +-------------------+-------+
    | Variable_name | Value |
    +-------------------+-------+
    | have_partitioning | YES |
    +-------------------+-------+
    1 row in set (0.00 sec)

Since the variable have_partitioning has a value of YES, it indicates MySQL server supports partitioning. Similarly, you can also verify the partitioning support by issuing the SHOW PLUGINS statement at the mysql prompt, as shown below.

mysql> SHOW PLUGINS;
    +------------+----------+----------------+---------+---------+
    | Name       | Status   | Type           | Library | License |
    +------------+----------+----------------+---------+---------+
    | binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
    | MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
    | ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |
    +------------+----------+----------------+---------+---------+
    11 rows in set (0.00 sec)

If the status is being displayed as ACTIVE against the partition plugin, it indicates the partitioning feature is enabled and available.

To disable the partitioning support option, start the MySQL server with the –skip – partition option. The value of have_partitioning is now DISABLED.

For creating partitioned tables, you can use most of the storage engines that are supported by the MySQL server.

It is important to note that all partitions of the partitioned table should be using the same storage engine.

To determine which storage engine the MySQL server supports, issue the SHOW ENGINES statement at the mysql prompt at shown below.

mysql> SHOW ENGINES\G
    *************************** 1. row ***************************
    Engine: MEMORY
    Support: YES
    Comment: Hash based, stored in memory, useful for temporary
    tables
    Transactions: NO
    XA: NO
    Savepoints: NO
    *************************** 2. row ***************************
    Engine: MyISAM
    Support: DEFAULT
    Comment: Default engine as of MySQL 3.23 with great performance
    Transactions: NO
    XA: NO
    Savepoints: NO
    *************************** 3. row ***************************
    Engine: InnoDB
    Support: YES
    Comment: Supports transactions, row-level locking, and foreign
    keys
    Transactions: YES
    XA: YES

Among all, the value against support column indicates whether an engine can be used or not.

A value of YES, NO and DEFAULT with the storage engine indicates if an engine is available, not available or available and currently set as default storage, respectively.

The following section outlines the various types of partitioning techniques that are supported by MySQL.

RANGE Partitioning

The following example illustrates the range-partitioning technique. First, we create an employees table, as shown below:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
    );

Among the list of columns, store_id can be used to partition the entire table based on the values available with it, as shown below:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
    )
    PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
    );

It is important to note that each partition in defined in order from lowest to highest.

For accommodating rows with some higher and/or greater values in the partitions, a clause named VALUE LESS THAN is used in the create table.. statement, as show below.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
    )
    PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN
    MAXVALUE
    );

MAXVALUE represents an integer value that is always greater than the largest possible value that is available.

LIST Partitioning

The following example demonstrates how to list partition a table based on a column value.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )
    PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
    );

Deleting data from a partition works more efficiently than deleting the data directly from the table.

HASH Partitioning

The following example demonstrates how to hash partition a table based on a column.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )
    PARTITION BY HASH(store_id)
    PARTITIONS 4;

If you do not include the PARTITIONS clause, the number of partitions defaults to 1.

KEY Partitioning

CREATE TABLE k1 (
        id INT NOT NULL PRIMARY KEY,
        name VARCHAR(20)
        )
        PARTITION BY KEY()
        PARTITIONS 2;

If there is no primary key available with the table but there is a unique key, then the unique key can be used as a partitioning key, as shown below.

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
    )
    PARTITION BY KEY()
    PARTITIONS 2;

You can obtain information about existing partitions in a number of ways.

Partition management refers to a set of activities that deals with the actions, such as:

• Adding
• Dropping
• Redefining
• Merging or Splitting

You can realize all of these actions by using the ALTER TABLE statement with a partition_option clause.

Only a single PARTITION BY, ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION, or COALESCE PARTITION clause can be used in a given ALTER TABLE statement.

You may also carry out a number of table and associated partition maintenance tasks by issuing a few SQL statements using the following options:

Partition pruning is an optimization technique that can be implemented for partitioned tables. The idea behind pruning is relatively simple and it can be described as "Do not scan a partition when there are no matching values." For example, suppose that you have a partitioned table t1 defined by the statement shown below:

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
    )
    PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
    );

Now, consider the case where you wish to obtain results from a query such as:

SELECT fname, lname, region_code, dob FROM t1 WHERE region_code > 125 AND region_code < 130;

It is easy to see that none of the rows which ought to be returned will be in either of the partitions p0 or p3; that is, we need to search only in partitions p1 and p2 to find matching rows. By doing so, we can save time and effort and find matching rows instead of having to scan all partitions in the table. This "cutting away" of unneeded partitions is known as "pruning."

The query optimizer can perform pruning whenever a WHERE condition can be reduced to either of the following two cases:

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

In the first case, the optimizer simply evaluates the partitioning expression for the value given. Then it determines and scans only the partition containing that value. In many cases, the equal sign can be replaced with other arithmetic comparison operators, including <, >, <= , >= , and <>.

Queries using BETWEEN in the WHERE clause can also take advantage of partition pruning.

In the second case, the optimizer evaluates the partitioning expression for each value in the list, creates a list of matching partitions, and then scans only the partitions in that list. Further pruning can be applied to short ranges, which the optimizer can convert into equivalent lists of values.

Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function.

Partition pruning can be implemented for all partition types. Though pruning for RANGE partitioning is already explained in the above section, this section illustrates how this technique can be applied to other types of partitions as well. For example, consider a table t3 that is partitioned by LIST, as shown below:

CREATE TABLE t3
    (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
    )
    PARTITION BY LIST(region_code)
    (
    PARTITION r0 VALUES IN (1, 3),
    PARTITION r1 VALUES IN (2, 5, 8),
    PARTITION r2 VALUES IN (4, 9),
    PARTITION r3 VALUES IN (6, 7, 10)
    );

Table t3 shows that the region_code column is limited to values between 1 and 10 (inclusive). So, when a select query is issued, such as the one below—

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3;

—the optimizer determines the partitions where values 1, 2 and 3 are found, which should be ro, r1 and skips those that remain (r2 and r3).

Similarly, for the tables that are partitioned by HASH and KEY, pruning is also possible in the cases when the WHERE clause uses a simple " = " relation against a column that is used in the partition expression. For example, consider the created table shown below:

CREATE TABLE t4 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
    )
    PARTITION BY KEY(region_code)
    PARTITIONS 8;

Any query that compares a column value with a constant can be pruned, as shown in the next line of code:

SELECT * FROM t4 WHERE region_code = 7;

Importantly, pruning can also be employed for short ranges, because the optimizer can turn such conditions into IN relations. For example, consider the next two queries with reference to the previously defined table t4:

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

In both of these cases, the WHERE clause is transformed by the optimizer into WHERE region_code IN (3, 4, 5). This optimization is used only if the range size is smaller than the number of partitions.

Pruning can be used only on integer columns of tables partitioned by HASH or KEY. But, for a table that is partitioned by KEY, has a composite primary key, and uses a composite partitioning key, it is possible to perform pruning for queries meeting the following two criteria:

• The query must have a WHERE clause of the form pkcol1 = c1 AND pkcol2 = c2 AND ... pkcolN = cN, where pkcol1..... pkcolN are the partitioning key columns and c1.....cN are constant values.
• All columns of the partitioning key must be referenced in the WHERE clause.

Database partitioning is a value-added database design technique that many data modelers and DBAs rely on to achieve various objectives.

a) Reduces the amount of data read operations for a typical SQL operations. As such, the overall response time is reduced.

b) Increases database performance by optimizing the database scan operations internally.

c) Simplifies data management so there is more control over how the data is organized inside a database.

d) Achieves a greater degree of query throughput by spreading data more logically.

1. MySQL Reference Manual, which is available through the website dev.mysql.com/doc/en/

2. MySQL Cookbook, by Paul DuBois

3. MySQL in a Nutshell, by Russell Dyer

4. MySQL Administrators Bible, by Sheeri K. Cabral and Keith Murphy.