MySQL 9.0 Release Notes
Explicit selection of partitions and subpartitions for rows
matching a given WHERE condition is supported.
Partition selection is similar to partition pruning, in that only
specific partitions are checked for matches, but differs in two
key respects:
The partitions to be checked are specified by the issuer of the statement, unlike partition pruning, which is automatic.
Whereas partition pruning applies only to queries, explicit selection of partitions is supported for both queries and a number of DML statements.
SQL statements supporting explicit partition selection are listed here:
The remainder of this section discusses explicit partition selection as it applies generally to the statements just listed, and provides some examples.
Explicit partition selection is implemented using a
PARTITION option. For all supported statements,
this option uses the syntax shown here:
PARTITION (partition_names)
partition_names:
partition_name, ...
This option always follows the name of the table to which the
partition or partitions belong.
partition_names is a comma-separated
list of partitions or subpartitions to be used. Each name in this
list must be the name of an existing partition or subpartition of
the specified table; if any of the partitions or subpartitions are
not found, the statement fails with an error (partition
'partition_name' doesn't
exist). Partitions and subpartitions named in
partition_names may be listed in any
order, and may overlap.
When the PARTITION option is used, only the
partitions and subpartitions listed are checked for matching rows.
This option can be used in a SELECT
statement to determine which rows belong to a given partition.
Consider a partitioned table named employees,
created and populated using the statements shown here:
SET @@SQL_MODE = '';
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id INT NOT NULL,
department_id INT NOT NULL
)
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);
You can see which rows are stored in partition
p1 like this:
mysql> SELECT * FROM employees PARTITION (p1);
+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 5 | Mary | Jones | 1 | 1 |
| 6 | Linda | Black | 2 | 3 |
| 7 | Ed | Jones | 2 | 1 |
| 8 | June | Wilson | 3 | 1 |
| 9 | Andy | Smith | 1 | 3 |
+----+-------+--------+----------+---------------+
5 rows in set (0.00 sec)
The result is the same as obtained by the query SELECT *
FROM employees WHERE id BETWEEN 5 AND 9.
To obtain rows from multiple partitions, supply their names as a
comma-delimited list. For example, SELECT * FROM
employees PARTITION (p1, p2) returns all rows from
partitions p1 and p2 while
excluding rows from the remaining partitions.
Any valid query against a partitioned table can be rewritten with
a PARTITION option to restrict the result to
one or more desired partitions. You can use
WHERE conditions, ORDER BY
and LIMIT options, and so on. You can also use
aggregate functions with HAVING and
GROUP BY options. Each of the following queries
produces a valid result when run on the
employees table as previously defined:
mysql> SELECT * FROM employees PARTITION (p0, p2)
-> WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name |
+----+----------------+
| 3 | Ellen Johnson |
| 4 | Jim Smith |
| 1 | Bob Taylor |
| 2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)
mysql> SELECT store_id, COUNT(department_id) AS c
-> FROM employees PARTITION (p1,p2,p3)
-> GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 | 2 |
| 5 | 3 |
+---+----------+
2 rows in set (0.00 sec)
Statements using partition selection can be employed with tables
using any of the supported partitioning types. When a table is
created using [LINEAR] HASH or
[LINEAR] KEY partitioning and the names of the
partitions are not specified, MySQL automatically names the
partitions p0, p1,
p2, ...,
p, where
N-1N is the number of partitions. For
subpartitions not explicitly named, MySQL assigns automatically to
the subpartitions in each partition
p the names
Xp,
Xsp0p,
Xsp1p, ...,
Xsp2p,
where XspM-1M is the number of subpartitions.
When executing against this table a
SELECT (or other SQL statement for
which explicit partition selection is allowed), you can use these
generated names in a PARTITION option, as shown
here:
mysql> CREATE TABLE employees_sub (
-> id INT NOT NULL AUTO_INCREMENT,
-> fname VARCHAR(25) NOT NULL,
-> lname VARCHAR(25) NOT NULL,
-> store_id INT NOT NULL,
-> department_id INT NOT NULL,
-> PRIMARY KEY pk (id, lname)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN (15),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (1.14 sec)
mysql> INSERT INTO employees_sub # reuse data in employees table
-> SELECT * FROM employees;
Query OK, 18 rows affected (0.09 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees_sub PARTITION (p2sp1);
+----+---------------+
| id | name |
+----+---------------+
| 10 | Lou Waters |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)
You may also use a PARTITION option in the
SELECT portion of an
INSERT ...
SELECT statement, as shown here:
mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO employees_copy
-> SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou | Waters | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
| 12 | Roger | White | 3 | 2 |
| 13 | Howard | Andrews | 1 | 2 |
| 14 | Fred | Goldberg | 3 | 3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)Partition selection can also be used with joins. Suppose we create and populate two tables using the statements shown here:
CREATE TABLE stores (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(30) NOT NULL
)
PARTITION BY HASH(id)
PARTITIONS 2;
INSERT INTO stores VALUES
('', 'Nambucca'), ('', 'Uranga'),
('', 'Bellingen'), ('', 'Grafton');
CREATE TABLE departments (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
)
PARTITION BY KEY(id)
PARTITIONS 2;
INSERT INTO departments VALUES
('', 'Sales'), ('', 'Customer Service'),
('', 'Delivery'), ('', 'Accounting');
You can explicitly select partitions (or subpartitions, or both)
from any or all of the tables in a join. (The
PARTITION option used to select partitions from
a given table immediately follows the name of the table, before
all other options, including any table alias.) For example, the
following query gets the name, employee ID, department, and city
of all employees who work in the Sales or Delivery department
(partition p1 of the
departments table) at the stores in either of
the cities of Nambucca and Bellingen (partition
p0 of the stores table):
mysql> SELECT
-> e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
-> s.city AS City, d.name AS department
-> FROM employees AS e
-> JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
-> JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
-> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name | City | department |
+-------------+---------------+-----------+------------+
| 14 | Fred Goldberg | Bellingen | Delivery |
| 5 | Mary Jones | Nambucca | Sales |
| 17 | Mark Morgan | Bellingen | Delivery |
| 9 | Andy Smith | Nambucca | Delivery |
| 8 | June Wilson | Bellingen | Sales |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)For general information about joins in MySQL, see Section 15.2.13.2, “JOIN Clause”.
When the PARTITION option is used with
DELETE statements, only those
partitions (and subpartitions, if any) listed with the option are
checked for rows to be deleted. Any other partitions are ignored,
as shown here:
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 8 | June | Wilson | 3 | 1 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)
mysql> DELETE FROM employees PARTITION (p0, p1)
-> WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
Only the two rows in partitions p0 and
p1 matching the WHERE
condition were deleted. As you can see from the result when the
SELECT is run a second time, there
remains a row in the table matching the WHERE
condition, but residing in a different partition
(p2).
UPDATE statements using explicit
partition selection behave in the same way; only rows in the
partitions referenced by the PARTITION option
are considered when determining the rows to be updated, as can be
seen by executing the following statements:
mysql> UPDATE employees PARTITION (p0)
-> SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
mysql> UPDATE employees PARTITION (p2)
-> SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 2 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
In the same way, when PARTITION is used with
DELETE, only rows in the partition
or partitions named in the partition list are checked for
deletion.
For statements that insert rows, the behavior differs in that
failure to find a suitable partition causes the statement to fail.
This is true for both INSERT and
REPLACE statements, as shown here:
mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)
mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)
For statements that write multiple rows to a partitioned table
that using the InnoDB storage engine:
If any row in the list following VALUES cannot
be written to one of the partitions specified in the
partition_names list, the entire
statement fails and no rows are written. This is shown for
INSERT statements in the following
example, reusing the employees table created
previously:
mysql> ALTER TABLE employees
-> REORGANIZE PARTITION p3 INTO (
-> PARTITION p3 VALUES LESS THAN (20),
-> PARTITION p4 VALUES LESS THAN (25),
-> PARTITION p5 VALUES LESS THAN MAXVALUE
-> );
Query OK, 6 rows affected (2.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(25) NOT NULL,
`lname` varchar(25) NOT NULL,
`store_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3, p4, p5) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
The preceding is true for both
INSERT statements and
REPLACE statements that write
multiple rows.
Partition selection is disabled for tables employing a storage
engine that supplies automatic partitioning, such as
NDB.