CODEWITHSUNDEEP
mysql
Victor
Victor

Reputation: 470

Use UNIQUE in AUTO_INCREMENT column to create an improved PRIMARY KEY

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

Upvotes: 0

Views: 51

Answers (1)

ysth
ysth

Reputation: 98398

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.

Upvotes: 1

Related Questions