Is there a general rule for where in the order of a primary-key index to place the partition key?

user_14465440 0
Viewed 251

Assume that I properly query the partition key in every query. Is there any sensible reason to place the partition key anywhere but first in line?

I feel like there's something I'm not understanding about how the index works. Assume MySQL and InnoDB.

I think I get that, ordinarily, you place the most selective keys first and the less selective ones later. And the partition key would ordinarily be one of the less selective ones. But if the partition key is included in every query, what difference does it make to include the partition key first? Wouldn't this help in other ways, too? E.g., I won't have to include the partition key in every index if it's up front in the primary-key index: queries using other indexes can borrow the primary key from the primary-key index consistent with the leftmost-key constraint.

And I don't know if an index itself is ever partitioned but it seems like it could be if it's a covering index. (Am I right?) If so, the partition key would have to be first, no, for the partitions to work?

E.g.:

CREATE TABLE  `fee` (
    `fi` INT ,
    `fo` INT ,
    PRIMARY KEY ( `fi` , `fo` ) ,
) ENGINE = INNODB
PARTITION BY RANGE ( `fi` ) (
   . . .
);

Or . . .

CREATE TABLE  `fee` (
    `fi` INT ,
    `fo` INT ,
    PRIMARY KEY ( `fo` , `fi` ) ,
) ENGINE = INNODB
PARTITION BY RANGE ( `fi` ) (
   . . .
);

Which, if either, is inherently better, and why or why not?

Thank you for your time.

database-partitioning partitioning mysql
2 Answers

The selectivity of the two columns doesn't matter as much as some people think.

If you were to query the table as:

SELECT ... FROM fee WHERE fi=? AND fo=?

Then what does it matter if it searches the B-tree by fi,fo or by fo,fi? It'll find the same record in the end, and it'll take roughly the same number of steps to do that. There's a theoretical difference, but in most cases it won't make a significant difference.

What's more important is if you have queries that only search for one or the other column of the primary key.

You mentioned that all queries search on the partition column, that's fi in this example. Do you have any queries that search on fi but not fo?

SELECT ... FROM fee WHERE fi=?

If fi were the first column of the primary key, this would do partition-pruning, and also use the PRIMARY KEY index because your search term is on the first column.

mysql> explain partitions select * from fee where fi = 175;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | fee   | p2         | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

Whereas if fi were the second column of the primary key, then it could do partition-pruning, but not use the index.

mysql> explain partitions select * from fee where fi = 175;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fee   | p2         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

Indexes are also partitioned. Think of partitioning as a series of completely separate tables, with the same columns and same indexes, just a subset of the rows. Once the query determines which partition to read, it does the query the same way it would against a non-partitioned table, choosing an index based on the query criteria. Will it use the primary key to search?

mysql> explain partitions select * from fee where fi = 175 and created_at < now();
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fee   | p2         | range | created_at    | created_at | 6       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

Here we see the condition on fi resulted in partition pruning, and yet the index on created_at was preferred by the optimizer. It searches that index in the respective partition.

Bill_Karwin_208600

"you place the most selective keys first and the less selective ones later" -- No. That is an old wives tale.

Put keys that are tested with '=' first is a simple and more important rule.

Think of a composite InnoDB BTree index as working this way. Concatenate all the columns together, then picture the BTree as having a single string as the key.

Putting the "partition key" first in an index is the least useful place! You are already pruning on that; having it in the index is actually redundant. However, it is necessary for any Unique key (that includes the `PRIMARY KEY').

Yes, you correctly observed that the PK columns are implicitly included in every secondary key, hence the partition key is included.

Note that if the partition key is not really part of a desired UNIQUE key, then the uniqueness constraint is not possible (in MySQL). However, the tacked-on PK is not part of the uniqueness constraint. Since MySQL is only willing to check uniqueness for one partition, you must include the partition key to also provide the semantics that states "Unique" across the entire table. (Yeah, it is a bit convoluted; live with it.)

In your example, if you do SELECT .. WHERE fi BETWEEN 1 and 2 AND fo=3, any index (the PK is an index) starting with fi would work harder than if fo were first in the index.

So, a Rule of Thumb is to move the partition key to the end of any index that includes it. (I have seen only one rare exception; I forget the details.)

user_17668310
Related