Archive for May, 2011
MySQL’s List Partition Key
While reviewing some material and explaining list partitioning in MySQL, I heard a rumor that sounded false. The rumor was that you can’t partition on anything other than the primary key column. That’s untrue, you can partition on another column provided it’s an integer column.
It appears the culprit that led to the rumor is a misunderstanding around ERROR 1503, which returns the following:
ERROR 1503 (HY000): A PRIMARY KEY must include ALL COLUMNS IN the TABLE's partitioning function |
The problem can be recreated by defining a table with an inline primary key constraint instead of an INDEX on the auto incrementing column. Here’s an example of how to create the error message:
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE franchise ( franchise_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , franchise_number INT UNSIGNED , franchise_name VARCHAR(20) , city VARCHAR(20) , state VARCHAR(20)) PARTITION BY LIST(franchise_number) ( PARTITION offshore VALUES IN (49,50) , PARTITION west VALUES IN (34,45,48) , PARTITION desert VALUES IN (46,47) , PARTITION rockies VALUES IN (38,41,42,44)); |
The table should be defined without the inline PRIMARY KEY constraint on the auto incrementing column when you want to partition on another column. An index on the auto incrementing column doesn’t conflict with primary key rules. The correct syntax for a list partition would be:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE franchise ( franchise_id INT UNSIGNED AUTO_INCREMENT , franchise_number INT UNSIGNED , franchise_name VARCHAR(20) , city VARCHAR(20) , state VARCHAR(20) , INDEX idx (franchise_id)) engine=innodb PARTITION BY LIST(franchise_number) ( PARTITION offshore VALUES IN (49,50) , PARTITION west VALUES IN (34,45,48) , PARTITION desert VALUES IN (46,47) , PARTITION rockies VALUES IN (38,41,42,44)); |
The same problem can manifest itself on range partitioning. The fix is the same and here’s a code example:
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE ordering ( ordering_id INT UNSIGNED AUTO_INCREMENT , item_id INT UNSIGNED , rental_amount DECIMAL(15,2) , rental_date DATE , INDEX idx (ordering_id)) PARTITION BY RANGE(item_id) ( PARTITION jan2011 VALUES LESS THAN (10000) , PARTITION feb2011 VALUES LESS THAN (20000) , PARTITION mar2011 VALUES LESS THAN (30000)); |
The MySQL 5.6 Partitioning Keys, Primary Keys, and Unique Keys article is the key reference. It’s unfortunate that MySQL can’t partition on something other than an integer with PARTITION BY LIST syntax (check the changes in MySQL 5.6 online documentation). As you’ll notice in my more recent comment back to Tim, you can when you change the SQL phrase to PARTITION BY LIST COLUMNS. MySQL now has another feature that the Oracle Database 11g supports, that is list partitioning on variable-length string columns.
There are a few required changes but here’s a working example. The index must work against the auto incrementing column. The example comes from my reply to Tim, who raised a great question.
CREATE TABLE franchise ( franchise_id INT UNSIGNED AUTO_INCREMENT , franchise_number INT UNSIGNED , franchise_name VARCHAR(20) , city VARCHAR(20) , state VARCHAR(20) , KEY idx (franchise_id)) engine=innodb PARTITION BY LIST COLUMNS (franchise_name) ( PARTITION m VALUES IN ('McDonald','Maggiano') , PARTITION p VALUES IN ('Pappa John','Pizza Hut') , PARTITION t VALUES IN ('Taco Bell','Taco Time')); |
While that works, the better approach creates two unique constraints. One unique constraint on the natural key of the franchise_number and franchise_name, which serves to optimize access to data sets looking data up in the table based on a natural key search. Another unique constraint on the surrogate (auto incrementing column) and the two natural key columns, which optimizes joins from foreign keys to the partitioned table’s primary key.
CREATE TABLE franchise ( franchise_id INT UNSIGNED AUTO_INCREMENT , franchise_number INT UNSIGNED , franchise_name VARCHAR(20) , city VARCHAR(20) , state VARCHAR(20) , UNIQUE ink (franchise_number, franchise_name) , UNIQUE enk (franchise_id, franchise_number, franchise_name)) engine=innodb PARTITION BY LIST COLUMNS (franchise_name) ( PARTITION m VALUES IN ('McDonald','Maggiano') , PARTITION p VALUES IN ('Pappa John','Pizza Hut') , PARTITION t VALUES IN ('Taco Bell','Taco Time')); |
Somebody asked for a composite partitioning (MySQL terminology is subpartitioning) example, so here it is:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE ordering ( ordering_id INT UNSIGNED AUTO_INCREMENT , item_id INT UNSIGNED , store_id INT UNSIGNED , rental_amount DECIMAL(15,2) , rental_date DATE , INDEX idx (ordering_id)) PARTITION BY RANGE(item_id) SUBPARTITION BY HASH(store_id) SUBPARTITIONS 4 ( PARTITION jan2011 VALUES LESS THAN (10000) , PARTITION feb2011 VALUES LESS THAN (20000) , PARTITION mar2011 VALUES LESS THAN (30000)); |
Hope this helps those researching ERROR 1503 on list or range partitioning.
MySQL Virtual Columns?
While preparing for next week’s classes, I ran across a new future feature of MySQL – the virtual column. It appears, according to the article, that MySQL will have virtual columns in MySQL 6.
The syntax is virtually identical to that found in Oracle databases. For example, here’s what suppose to work in the future:
1 2 3 4 5 | CREATE TABLE salary ( salary_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , salary DOUBLE NOT NULL , bonus DOUBLE , compensation DOUBLE AS (salary + bonus)); |
It’s virtually (excuse the pun) how it would work in Oracle Database 11g. I wonder what else we see move over into MySQL. Just a note, virtual columns moved from Oracle RDB (a prior acquisition from Digital Equipment Corporation in 1996) to the Oracle Database 11g.
Equivalent syntax in Oracle Database 11g would be like this for concatenation:
1 2 3 4 5 | CREATE TABLE employee ( employee_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20) , full_name VARCHAR2(41) AS (first_name || ' ' || last_name)); |
And like this for a math operation (like the one noted above for a future release of MySQL):
1 2 3 4 5 | CREATE TABLE salary ( salary_id NUMBER CONSTRAINT pk_salary PRIMARY KEY , salary NUMBER(15,2) CONSTRAINT nn_salary_01 NOT NULL , bonus NUMBER(15,2) , compensation NUMBER(15,2) AS (salary + bonus)); |
Subscribe via RSS