MySQL: The Most Chosen Database

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.

Section 2

MySQL: The Most Chosen Database

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.

Section 3

What is Database Partitioning

Partitioning is a database design technique with details as follows

Major Abilities of Partitioning

1. Splits large database into smaller and more manageable ones.

2. Simplifies the overall data management and operations.

3. Enhances the overall performance of the database.

4. Assists the MySQL Optimizer.

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

Section 4

What is Database Partitioning

Partitioning is a database design technique with details as follows

Major Abilities of Partitioning

1. Splits large database into smaller and more manageable ones.

2. Simplifies the overall data management and operations.

3. Enhances the overall performance of the database.

4. Assists the MySQL Optimizer.

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

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.

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.

5. To delete all the data from a partition, use the DROP PARTITION clause. ALTER TABLE table_name DROP PARTITION partition_name;

6. To preserve the table definition, use TRUNCATE TABLE statement.

7. To change the partition without losing any data, use the REORGANIZE PARTITION clause. ALTER TABLE table_name REORGANIZE PARTITION [partition_name INTO (partition_definitions)]

8. With range partitions, you can add new partitions ONLY to the high end of the partition list.

9. Adding a new partition in between or before an existing partition will result in error.

10. You cannot add a new LIST PARTITION encompassing any values that are already included in the value list of an existing partition. This will result in an error.

11. The REORGANIZE PARTITION clause may also be used for merging adjacent partitions.

12. The REORGANIZE PARTITION clause cannot be used for changing the table's partitioning type. For example, you cannot change Range partitions to Hash partitions, or vice-versa.

Managing Hash and Key Partitions – a Quick Glance

1. When it comes to making changes to partitioning setup, it is similar with hash and key partitions.

2. You cannot drop hash or key partitions.

3. You can merge hash or key partitions by using an ALTER TABLE statement with the coalesce partition clause, as shown below: ALTER TABLE table_name COALESCE PARTITION 4;

4. The number following the coalesce partition clause refers to the number of partitions to remove from the table

5. Attempting to remove more partitions than the table has will lead to an error.

Section 17

Partitions Maintenance

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

Statement

Description

Equivalent SQL Statement

REBUILDING PARTITIONS

Used for rebuild ing a partition and for defragmentation.

ALTER TABLE table_ name REBUILD PARTITION p0, p1;

OPTIMIZING PARTITIONS

Used to reclaim any unused space and defragment partition data files.

ALTER TABLE table_name OPTIMIZE PARTITION p0, p1;.

ANALYZING PARTITIONS

Used to store key distributions about partitions.

ALTER TABLE table_name ANALYZE partition p3;

REPAIRING PARTITIONS

Used for repairing any corrupted partitions.

ALTER TABLE table_name REPAIR partition p0, p1;

CHECKING PARTITIONS

Used for checking errors in partitions

ALTER TABLE table_name CHECK partition p1;

Section 18

Partitions Maintenance

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

Statement

Description

Equivalent SQL Statement

REBUILDING PARTITIONS

Used for rebuild ing a partition and for defragmentation.

ALTER TABLE table_ name REBUILD PARTITION p0, p1;

OPTIMIZING PARTITIONS

Used to reclaim any unused space and defragment partition data files.

ALTER TABLE table_name OPTIMIZE PARTITION p0, p1;.

ANALYZING PARTITIONS

Used to store key distributions about partitions.

ALTER TABLE table_name ANALYZE partition p3;

REPAIRING PARTITIONS

Used for repairing any corrupted partitions.

ALTER TABLE table_name REPAIR partition p0, p1;

CHECKING PARTITIONS

Used for checking errors in partitions

ALTER TABLE table_name CHECK partition p1;

Section 19

Partition Pruning

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:

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.

Section 20

Partition Pruning

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:

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.

Section 21

Implementing Partition Pruning Technique

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:

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:

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.

Section 22

Implementing Partition Pruning Technique

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:

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:

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.

Section 23

About 'Explain Partition' Statement

1. Generally, EXPLAIN statement is used to obtain information about how MySQL executes a statement.