CO_HASH partitioning routes rows with correlated partition key values to the same physical partition. For example, in an e-commerce orders table where every row's order_id and buyer_id end in the same digits, CO_HASH ensures that reads and writes can be served by either column without crossing partition boundaries—eliminating cross-database transactions.
Prerequisites
PolarDB-X 5.4.18-17047709 or later.
When to use CO_HASH
CO_HASH is designed for tables where two or more columns share a predictable structural relationship, such as a common suffix or prefix. A CO_HASH partitioned table lets you route data by any of those columns independently while guaranteeing that all matching values land in the same partition (partition pruning on each column separately).
Use CO_HASH when:
At least two columns always share the same suffix or prefix—for example, the last N digits are identical across rows.
Queries filter on any one of those columns independently, and each query must hit a single partition.
If the columns have no guaranteed structural similarity, use HASH or KEY partitioning instead. CO_HASH does not verify similarity at write time—it only checks that the routing result is consistent. For a full comparison, see the partition table types and policies overview.
Syntax
CREATE TABLE ...
PARTITION BY CO_HASH(partition_expr_list)
PARTITIONS number;
partition_expr_list:
partition_expr, partition_expr [, partition_expr, ...]
partition_expr:
partition_column
| partition_func(partition_column)
-- Supported partitioning functions:
partition_func:
RIGHT
| LEFT
| SUBSTR
| SUBSTRINGHow routing works
CO_HASH extracts a substring from each partition key column using the specified function, converts the result to an integer, and applies a modulo operation against the partition count to determine the target partition.
For a table with 8 partitions, the routing for order_id = 1001234 and buyer_id = 1001234 using RIGHT(column, 6) works as follows:
| Step | order_id | buyer_id |
|---|---|---|
| Extract last 6 digits | 001234 | 001234 |
| Convert to integer (strip leading zeros) | 1234 | 1234 |
MOD(1234, 8) | 2 | 2 |
| Target partition | Partition 2 | Partition 2 |
Both columns produce the same routing result, so the row lands in the same partition regardless of which column the query uses.
PolarDB-X automatically strips leading zeros from integer-type columns after truncation. For example, RIGHT(1000034, 4) yields 0034, which is converted to 34 before routing. This ensures that integer columns with matching values always route to the same partition even when truncation produces a zero-padded string.
Create a CO_HASH partitioned table
The following example creates an orders table partitioned on the last six digits of both order_id and buyer_id. Because those digits match in every row, the table routes queries correctly regardless of which column is used.
CREATE TABLE t_orders (
id BIGINT NOT NULL AUTO_INCREMENT,
seller_id BIGINT,
order_id BIGINT,
buyer_id BIGINT,
order_time DATETIME NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY CO_HASH(
RIGHT(`order_id`, 6), -- last 6 digits of order_id
RIGHT(`buyer_id`, 6) -- last 6 digits of buyer_id
)
PARTITIONS 8;For additional partitioning functions, see Partitioning functions.
Limitations
General limits
| Limit | Value |
|---|---|
| Maximum partitions per table | 8,192 (default) |
| Maximum partition key columns per partition key | 5 (default) |
| Supported partitioning functions | RIGHT, LEFT, SUBSTR, SUBSTRING |
Additional constraints:
Nested partitioning functions are not allowed. For example,
SUBSTR(SUBSTR(c1, -6), 4)is invalid.All partition key columns must have identical character sets, collation, length, and precision.
Supported data types
| Category | Types |
|---|---|
| Integer | BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED |
| Date and time | DATETIME, DATE, TIMESTAMP |
| String | CHAR, VARCHAR |
| Fixed-point | DECIMAL (fractional digits must be 0) |
Partition key column constraints
Similarity is your responsibility. PolarDB-X routes rows based on the partition expressions you define—it does not verify that the column values actually satisfy the similarity you assumed. If c1 = 1001234 and c2 = 1320 are inserted into a table where you defined RIGHT(c1, 4) and RIGHT(c2, 4) as the partition expressions, PolarDB-X routes them based on 1234 and 1320 respectively. If those values resolve to different partitions, the INSERT fails with an error—but no semantic validation of the similarity assumption occurs before routing.
DML constraints
Because CO_HASH relies on partition key values being consistent across columns, modifications to those values are restricted.
INSERT and REPLACE
If the values in the VALUES clause route different partition key columns to different partitions, the statement is rejected.
-- Invalid: last 4 digits of c1 (1234) and c2 (5678) route to different partitions
INSERT INTO t1 (c1, c2) VALUES (1001234, 1005678);
-- Valid: last 4 digits of c1 (1234) and c2 (1234) route to the same partition
INSERT INTO t1 (c1, c2) VALUES (1001234, 1001234);UPDATE and UPSERT
If the SET clause modifies any partition key column, all partition key columns must be updated in the same statement. After the update, all partition key columns must still route to the same partition.
-- Invalid: only c1 is updated; c2 is not updated at the same time
UPDATE t1 SET c1 = 'xx' WHERE id = 1;
-- Valid: both partition key columns are updated together
UPDATE t1 SET c1 = 'xx', c2 = 'yy' WHERE id = 1;If the updated values route to different partitions, the statement is rejected.
Global secondary index (GSI)
If a DML operation on a primary table—such as INSERT or UPDATE—causes the GSI partition key columns to route to different partitions, the operation is rejected.
What's next
Partitioning functions — full reference for
RIGHT,LEFT,SUBSTR, andSUBSTRINGPartition table types and policies overview — compare CO_HASH, HASH, and KEY partitioning