Arquivo da tag: mysql

MySQL 5.6 and the new MySQL Partitioning resources

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 new database to support a system responsible to be the interface with sales department. Obviously, that system must be as much faster as it can to get round expending customers time or lose the opportunity to sell more products. With this points in mind, we’ll create the following [example] table in order to fit some performance requirements:

[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,
->   value decimal(10,2) not null,
->   payment_date datetime not null
-> ) 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 (0.05 sec)

Now that you’re looking forward to retrieve data from sales of the January actual year (I’ll enter a row to avoid an empty result set).
mysql> insert into t1 set id=1, value='10.00',
-> payment_date='2011-01-01 00:00:00';
Query OK, 1 row affected (0.00 sec)

And now, the new resource supported in MySQL 5.6 – how to retrieve data from partitioned table selecting rows just from a specific partition (I remember you that you can check existing partition names querying INFORMATION_SCHEMA.PARTITIONS table) :
mysql> select * from t1 partition(p0);
+----+-------+---------------------+
| id | value | payment_date |
+----+-------+---------------------+
| 1 | 10.00 | 2011-01-01 00:00:00 |
+----+-------+---------------------+
1 row in set (0.01 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…

See this below:
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.03 sec)

mysql> explain select * from t1 partition(p0)\G # (1)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
1 row in set (0.04 sec)

mysql> explain select * from t1\G # (2)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11
Extra:
1 row 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/

mysql_labs_logo