Overlooked MySQL 5.6 New Features – WL#5217
There’s a lot of great new features in MySQL 5.6 DMRs – almost too many to keep track of. And while a lot of (justified) attention is given to the headline-grabbing features of 5.6 (memcached APIs! global transaction ids! improved PERFORMANCE_SCHEMA!), I’m often curious about the new features that don’t make as big a splash. I thought I would look at one such new feature – WorkLog #5217. I’m not telling you what this WorkLog is yet; test your knowledge of 5.6 features by seeing if you can figure it out from the following scenario. Imagine the following table data:
mysql> SELECT * FROM p; +------+------+ | a | b | +------+------+ | 1 | 1 | | 11 | 1 | | 21 | 1 | +------+------+ 3 rows in set (0.00 sec)
OK, try to write an UPDATE statement that increments b for the row where a = 11. Easy, right?
UPDATE p SET b = b+1 WHERE a = 11;
OK, now do it without a WHERE clause. Maybe that made you think for a minute, but perhaps you came up with something like this:
UPDATE p SET b = b+1 ORDER BY a = 11 DESC LIMIT 1;
Nice! Now do it without ORDER BY or LIMIT. Can you do it?
There’s a hint in the table structure:
mysql> SHOW CREATE TABLE p\G *************************** 1. row *************************** Table: p Create Table: CREATE TABLE `p` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
You’ve probably figured out that 5.6 adds the ability to explicitly restrict statements to specific partitions, even if you haven’t found or reviewed the excellent documentation in the manual. In 5.6, the following works:
mysql> UPDATE p PARTITION(p1) SET b = b+1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM p; +------+------+ | a | b | +------+------+ | 1 | 1 | | 11 | 2 | | 21 | 1 | +------+------+ 3 rows in set (0.00 sec)
The documentation on this is very good, and provides some useful examples of how this might prove useful, including showing how this works not only with UPDATE statements, but INSERT, SELECT, DELETE, REPLACE and LOAD DATA/XML statements. There’s also the WorkLog and associated bugs.mysql.com feature requests. The documentation says the following about UPDATE statements:
UPDATEstatements using explicit partition selection behave in the same way; only rows in the partitions referenced by the
PARTITIONoption are considered when determining the rows to be updated, as can be seen by executing the following statements.
It then provides an example that is similar to the solution for the problem posed above. I still had one question, though – what would happen if I did an UPDATE on a partition key column that moves a row from one partition to another? I get an error:
mysql> UPDATE p PARTITION(p1) SET a = 22 WHERE a = 11; ERROR 1745 (HY000): Found a row not matching the given partition set
But if I include both the source partition and the target partition, all’s well:
mysql> UPDATE p PARTITION(p1,p2) SET a = 22 WHERE a = 11; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
How do I know the data was actually moved from p1 to p2, and that mysqld isn’t just pulling my leg? Easy:
mysql> SELECT * FROM p PARTITION(p2); +------+------+ | a | b | +------+------+ | 21 | 1 | | 22 | 1 | +------+------+
Awesome. You might want to download the 5.6.5 Development Milestone Release yourself and try this and other new 5.6 features (you’ll need to click on the “Development Releases” tab to access it, something which took me longer to figure out than I care to admit).