There are lots of new features on MySQL 5.6 related to the MySQL Partition Engine that we can apply on database tables. With MySQL new version, besides the performance improvements provided by the partitioned tables (which resource we have since MySQL 5.1), the database administrators might improve their environments architecture & design in order to better retrieve information selecting data just from the specific table partition – partition pruning.

We will start this post creating a table that will store data from the product payment system’s process and it will be partitioned using the RANGE() partition function with the MONTH() function nested. If you want to know more about partitioned tables using the RANGE() partition function, click here (a post written in Portuguese).

Scenario

Imagine you’re developing a new database to support a system responsible to be the interface with sales department. Obviously, that system must be as faster as it can to get round expending customers time or lose the opportunity to sell more products (generally sales guys are very greedy and the organization platform systems must support them). With this point in mind, we’ll create the following [example] table in order to fit some performance requirements using a partitioning example by month():

[root@innodbserver mysql]# mysql -u root
Welcome to the MySQL monitor.
Your MySQL connection id is 1
5.6.2-m5-log MySQL Community Server (GPL)

mysql> use test
Database changed

mysql> CREATE TABLE t1 (
    -> id int not null auto_increment,
    -> value decimal(10,2) not null,
    -> payment_date datetime not null,
    -> PRIMARY KEY(id,payment_date)
    -> ) PARTITION BY RANGE(MONTH(payment_date)) (
    -> PARTITION p0 VALUES LESS THAN(02),
    -> PARTITION p1 VALUES LESS THAN(03),
    -> PARTITION p2 VALUES LESS THAN(04),
    -> PARTITION p3 VALUES LESS THAN(05),
    -> PARTITION p4 VALUES LESS THAN(06),
    -> PARTITION p5 VALUES LESS THAN(07),
    -> PARTITION p6 VALUES LESS THAN(08),
    -> PARTITION p7 VALUES LESS THAN(09),
    -> PARTITION p8 VALUES LESS THAN(10),
    -> PARTITION p9 VALUES LESS THAN(11),
    -> PARTITION P10 VALUES LESS THAN(MAXVALUE)
    -> );
Query OK, 0 rows affected (5.73 sec)

Let’s load some data into the table so as we can work with some partitioning features. Perhaps soon I may update this post with a stored procedure to populate table’s partitions in a WHILE loop.

insert into test.t1 set id=null, value='1.00', payment_date=date_sub(now(), interval 1 month);
insert into test.t1 set id=null, value='2.00', payment_date=date_sub(now(), interval 2 month);
insert into test.t1 set id=null, value='3.00', payment_date=date_sub(now(), interval 3 month);
insert into test.t1 set id=null, value='4.00', payment_date=date_sub(now(), interval 4 month);
insert into test.t1 set id=null, value='5.00', payment_date=date_sub(now(), interval 5 month);
insert into test.t1 set id=null, value='6.00', payment_date=date_sub(now(), interval 6 month);
insert into test.t1 set id=null, value='7.00', payment_date=date_sub(now(), interval 7 month);
insert into test.t1 set id=null, value='8.00', payment_date=date_sub(now(), interval 8 month);
insert into test.t1 set id=null, value='9.00', payment_date=date_sub(now(), interval 9 month);
insert into test.t1 set id=null, value='10.00', payment_date=date_sub(now(), interval 10 month);
insert into test.t1 set id=null, value='11.00', payment_date=date_sub(now(), interval 11 month);
insert into test.t1 set id=null, value='12.00', payment_date=date_sub(now(), interval 12 month);

And now, the new resource supported in MySQL 5.6 – how to retrieve data from partitioned table selecting rows just from a specific partition:

mysql> select id, concat('R$ ',value) as amount, payment_date from test.t1 partition(p5);
+----+---------+---------------------+
| id | amount  | payment_date        |
+----+---------+---------------------+
| 1  | R$ 1.00 | 2014-06-22 21:19:26 |
+----+---------+---------------------+
1 row in set (0.00 sec)

You can check existing partition names, expressions and current rows querying INFORMATION_SCHEMA.PARTITIONS table:

mysql> select table_schema, table_name, partition_name, table_rows
    -> from information_schema.partitions where table_name='t1' and table_schema='test'\g
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| test         | t1         | p0             |          1 |
| test         | t1         | p1             |          1 |
| test         | t1         | p2             |          1 |
| test         | t1         | p3             |          1 |
| test         | t1         | p4             |          1 |
| test         | t1         | p5             |          1 |
| test         | t1         | p6             |          1 |
| test         | t1         | p7             |          1 |
| test         | t1         | p8             |          1 |
| test         | t1         | p9             |          1 |
| test         | t1         | P10            |          2 |
+--------------+------------+----------------+------------+
11 rows in set (0.00 sec)

I created a table partitioned by RANGE() partition function and it is using the MONTH() MySQL built-in function nested, which will become impossible MySQL Partition Engine to use the engine resource called Partitioning Pruning. It is true, but, since we are stating from what partition it will retrieve data, partition pruning doesn’t care in this case. To have better results, I’ll insert some new rows into the created table and then, I’ll SELECT rows from table using EXPLAIN in two scenarios, (1) it will read rows from a specific partition to force the partition pruning resource and (2) it will read rows from all partitions – you’ll check this observing the output of the EXPLAIN PARTITIONS…

See this below:

#
#: selecting rows from all partitions
#
mysql> explain partitions select id, concat('R$ ', value) as amount, payment_date from test.t1 order by value\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,P10
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: Using filesort
1 row in set (0.00 sec)

#
#: selecting rows from a specific partition - partition pruning
#
mysql> explain partitions select id, concat('R$ ', value) as amount, payment_date from test.t1 partition(p6)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p6
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: NULL
1 row in set (0.00 sec)

This resource or feature can be considered by the database administrator to improve the database design and queries’ performance. It’s much better to select rows from a single partition than scan all the index or table searching for rows. Not just for SELECT, but when one thinks about a good strategy for data purge or move a slice of the table’s data to a history database, it’s possible to just exchange partition among partitioned tables or even drop/truncate it.

mysql> alter table test.t1 drop partition p10;
Query OK, 0 rows affected (1.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select table_name, partition_name from information_schema.partitions where table_name='t1' and table_schema='test';
+------------+----------------+
| table_name | partition_name |
+------------+----------------+
| t1         | p0             |
| t1         | p1             |
| t1         | p2             |
| t1         | p3             |
| t1         | p4             |
| t1         | p5             |
| t1         | p6             |
| t1         | p7             |
| t1         | p8             |
| t1         | p9             |
+------------+----------------+
10 rows in set (0.01 sec)

As you can see, we can force the partition pruning when using named partitions on SELECT through the functions PARTITION(). This feature is planned to MySQL 5.6 and you can download that version from MySQL Labs ->http://labs.mysql.com/

Tags: innodb, month, mysql, mysql 5.6, partitioning, pruning, range

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.