There are too functional new features on MySQL 5.6 regarding to the MySQL Partition Engine we can apply on database tables.
With that new version, besides of the performance improvements provided by the partitioned tables, the database administrators could benefit their environments in order to better retrieve information selecting data just from the specific table partition. We will start this post creating a table that will store data of the product payment system and this table will be partitioned using RANGE() partition function with MONTH() function nested.
If you need to know more about partitioned tables by RANGE(), click here (post wrote in Portuguese).
Scenario
Imagine that you are developing new database to support a new system responsible to be the interface with sales. Obviously, this system could be faster as much as it can to avoid expend the customer’s time or lose the opportunity to sell more products due to a snail system. With this mind, we can create the follow [example] table in order to fit some performance requirements:
[root@innodbserver mysql]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.2-m5-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 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)
Imagine now that you’re looking forward to retrieve data from sales of the January’s actual year. I will enter a row to avoid empty result sets.
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)
After the above INSERT has been done, we can show how to retrieve data from partitioned table selecting a specific partition:
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)
OK, I created a table applying partition by RANGE() and use MONTH() function nested what will impedes MySQL Partition Engine to use the engine’s 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 better see more about it, I will insert some new rows and then get queries explains to state just rows from partition “p0″ and other one with all table rows.
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 # reading just the partition p0 rows (is it forced pruning?)
*************************** 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 # reading all partitions rows
*************************** 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 see, we could prune now using namely partition on SELECT. This is a feature of palnned MySQL 5.6 that you can download from MySQL Labs -> http://labs.mysql.com/
See you soon.
