title: Combine a covering index with a unique constraint in MySQL
body: <p>Consider a table with the following fields:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>Notice that the field name has a unique constraint.</p>
  
  <p>Lets say I want to optimize the following query:</p>
  
  <pre><code>SELECT name, value
  FROM my_table
  WHERE name = 'my_name'
  </code></pre>
  
  <p>There already is an index for the <code>name</code> field (due to the <em>unique</em> constraint), but it would be even better to have a covering index for the field <code>value</code> as well.</p>
  
  <p>With just one index for the unique constraint, nothing surprising happens when I run the <code>EXPLAIN</code> command:</p>
  
  <pre><code>mysql&gt; EXPLAIN
      -&gt; SELECT name, value
      -&gt; FROM my_table
      -&gt; 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 |       |
  +----+-------------+----------+-------+---------------+------+---------+-------+------+-------+
  </code></pre>
  
  <p>Now if I try to add a covering index,</p>
  
  <pre><code>ALTER TABLE my_table ADD INDEX idx_name_value (name, value);
  </code></pre>
  
  <p>it appears as a candidate for the query, but is not selected!</p>
  
  <pre><code>mysql&gt; EXPLAIN
      -&gt; SELECT name, value
      -&gt; FROM my_table
      -&gt; 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 |       |
  +----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+
  </code></pre>
  
  <p>Notice that if I remove the unique constraint,</p>
  
  <pre><code>ALTER TABLE my_table DROP INDEX name;
  </code></pre>
  
  <p>the covering index works as expected:</p>
  
  <pre><code>mysql&gt; EXPLAIN
      -&gt; SELECT name, value
      -&gt; FROM my_table
      -&gt; 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 |
  +----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+
  </code></pre>
  
  <p>So how can I use a covering index and still have a unique constraint?</p>