Combine a covering index with a unique constraint in MySQL

Question

Consider a table with the following fields:

mysql> DESCRIBE my_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| pk    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   | UNI |         |       |
| value | varchar(255) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Notice that the field name has a unique constraint.

Lets say I want to optimize the following query:

SELECT name, value
FROM my_table
WHERE name = 'my_name'

There already is an index for the name field (due to the unique constraint), but it would be even better to have a covering index for the field value as well.

With just one index for the unique constraint, nothing surprising happens when I run the EXPLAIN command:

mysql> EXPLAIN
    -> SELECT name, value
    -> FROM my_table
    -> WHERE name = "my_name";
+----+-------------+----------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | my_table | const | name          | name | 62      | const |    1 |       |
+----+-------------+----------+-------+---------------+------+---------+-------+------+-------+

Now if I try to add a covering index,

ALTER TABLE my_table ADD INDEX idx_name_value (name, value);

it appears as a candidate for the query, but is not selected!

mysql> EXPLAIN
    -> SELECT name, value
    -> FROM my_table
    -> WHERE name = "my_name";
+----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys       | key  | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | my_table | const | name,idx_name_value | name | 62      | const |    1 |       |
+----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+

Notice that if I remove the unique constraint,

ALTER TABLE my_table DROP INDEX name;

the covering index works as expected:

mysql> EXPLAIN
    -> SELECT name, value
    -> FROM my_table
    -> WHERE name = "my_name";
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys  | key            | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | my_table | ref  | idx_name_value | idx_name_value | 62      | const |    1 | Using where; Using index |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+

So how can I use a covering index and still have a unique constraint?

Answer 1

There already is an index for the name field (due to the unique constraint), but it would be even better to have a covering index for the field value as well.

No. With the unique constraint you get the index for name "for free". And because you are searching only with name, you don't need the covering (combined) index with value.

You only get benefits of an index on value when you use queries with value in the where clause.

A combined index (name, value) would even an overhead when you never search for value (and so never use the index). On insert/update operations the index have to be updated and this could cost performance.