Use UNIQUE in AUTO_INCREMENT column to create an improved PRIMARY KEY

Question

Taking into account that in MySQL the AUTO_INCREMENT column must be part of the PRIMARY KEY, I would like to ask if makes sense using UNIQUE key in that AUTO_INCREMENT column to be able to create a composite PRIMARY KEY without defining it as the first PK index.

I created a table like this:

CREATE TABLE `test` (
id INT NOT NULL auto_increment,
a  INT NOT NULL,
b  INT NOT NULL,
c  INT NOT NULL,
PRIMARY KEY (id, a, b));

My purpose is to search by (a) or (a, b) and then sort it according to id. I would like to create a PRIMARY KEY as (a, b, id) but it is not allowed by definition. Besides, as documentation states, a PRIMARY KEY composed of (x, y, z) will take advantage of PRIMARY KEY only if you search by (x), (x, y) or (x, y, z), but not if you search by (y) or (y , z) (or anything else). I verified it. Indeed, with the above PRIMARY KEY (id, a, b) definition, any query searching for a does not use any PRIMARY KEY:

DESCRIBE SELECT `a`, `b` FROM `test` WHERE `a` = 0;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | test  | index | NULL          | PRIMARY | 12      | NULL |    1 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

I decided to cheat a bit and redefined id as UNIQUE KEY (so that it is still regarded as a KEY) and then recreated a PRIMARY KEY as (a, b, id). This works:

ALTER TABLE `test` DROP PRIMARY KEY, ADD UNIQUE KEY (`id`), ADD PRIMARY KEY (`a`, `b`, `id`)

Now, searching by a uses PRIMARY KEY!

DESCRIBE SELECT `a`, `b` FROM `test` WHERE `a` = 0;
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | test  | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

That changes this original PRIMARY KEY (id, a, b) with id in the first position to another scheme with a in the first position:

# before
SHOW KEYS FROM `test`;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | PRIMARY  |            2 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | PRIMARY  |            3 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

# after
SHOW KEYS FROM `test`;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | PRIMARY  |            2 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | PRIMARY  |            3 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Does this have any performance penalties or any disadvantages I did not see? This solutions looks to me simpler than triggering stored procedures to increment the index (like this solution).

Thank you in advance

*My purpose is to search by (a) or (a, b) and then sort it according to id.* Make primary key = `(id)` and add (unique) indices by `(a, id)` and `(a, b, id)`. — Akina, Sep 30 '20 at 06:15
That also looks like a good solution. Why `id` must be alone a `PRIMARY KEY`? I thought secondary indices are arranged after `PRIMARY KEY` — Victor, Sep 30 '20 at 06:22
*Why id must be alone a PRIMARY KEY?* 1) This matches the rule that AI must be a part of PK. 2) This decreases the actual size of another indices which always includes PK as hidden index component if it is not included explicitly. PS. In InnoDB primary key is clustered always. — Akina, Sep 30 '20 at 06:26

ysth · Accepted Answer · 2020-09-30T09:37:22.540

1

It seems like you can achieve your goals by having id as the sole primary key and indexes on (a,b) and (a) - recent versions of mysql will implicitly add the primary key to the end of all other indexes. To avoid a separate sort step, you may need to hint your a= query to use the a index instead of the a,b index. If id alone is unique, there's no reason to have a longer primary key.

edited Sep 30 '20 at 09:37

answered Sep 30 '20 at 06:39

ysth

96,171
6
121
214

Thank you. I checked the query with that structure and uses those keys. – Victor Sep 30 '20 at 09:06
One more thing: how could I partition that table according to `a`? It must be part of `PRIMARY KEY` – Victor Sep 30 '20 at 09:28
1

Partitions are only very rarely a good idea. I suppose you'd just make the primary key (a,id) then, plus a separate (a,b) index – ysth Sep 30 '20 at 09:36
I mentioned partitioning because that table actually contains 180 million registers. Table has very few columns and the different values of `a` are less than 10k, so I believe that partitioning could speed up things. Thanks! – Victor Sep 30 '20 at 09:43
if your queries always have a=, that could well be true – ysth Sep 30 '20 at 16:15

Use UNIQUE in AUTO_INCREMENT column to create an improved PRIMARY KEY

1 Answers1