MySQL multiple unique keys, drawback

Question

Is there a drawback to crate a unique key containing fields A, B, C and then create another one with fields C, B, A, so MySQL will take advantage of the index in case of searching only with A and only with C?

@BrianRoach: I am just a beginner, but accoding to MySQL docs, it will help, I think... or not? — Blazer, Dec 21 '13 at 04:39
Actually, I forgot with mySQL you get a benefit on leftmost. See: http://stackoverflow.com/a/1823818/302916. That said ... if you're never going to use the composite key, why waste the space? You need to figure out what your use patterns are and index accordingly. — Brian Roach, Dec 21 '13 at 04:44

score 1 · Accepted Answer · answered Dec 21 '13 at 06:30

You don't want to create an additional composite UNIQUE constraint. The case when you access your data by only A is already covered by existing index (a, b, c). If you need to support queries with access path only by c then you can create an index just on c.

If your schema looks something like

mysql> create table tablex 
    -> (
    ->   a int not null, 
    ->   b int not null, 
    ->   c int not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tablex values (1, 2, 3),(2, 3, 4),(1, 3, 3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create unique index idx_abc_unique on tablex (a, b, c);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you filter on A only you'll see that unique index is properly used because A is the leftmost prefix (keylen = 4) of the index. Extra column in EXPLAIN results shows Using index.

mysql> explain select * from tablex where a = 1;
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tablex | ref  | idx_abc_unique | idx_abc_unique | 4       | const |    1 | Using index |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Now if you try filter on C then you'll see a different story. EXPLAIN shows that MySQL is in fact using unique index but is doing full index scan (type = index) with a filter predicate identified by Using where in Extra column.

mysql> explain select * from tablex where c = 3;
+----+-------------+--------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key            | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tablex | index | NULL          | idx_abc_unique | 12      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Here is SQLFiddle demo

If we create an explicit index on C

mysql> create index idx_c on tablex (c);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

and take a look at EXPLAIN we'll see again Using index.

mysql> explain select * from tablex where c = 3;
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | tablex | ref  | idx_c         | idx_c | 4       | const |    1 | Using index |
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Here is SQLFiddle demo

MySQL multiple unique keys, drawback

1 Answers1