title: Does adding a unique constraint slow down things?
body: <p>I have three columns in my table. </p>
  
  <pre><code>+-----------+-----------------------+------+-----+---------+-------+
  | Field     | Type                  | Null | Key | Default | Extra |
  +-----------+-----------------------+------+-----+---------+-------+
  | hash      | mediumint(8) unsigned | NO   | PRI | 0       |       | 
  | nums      | int(10) unsigned      | NO   | PRI | 0       |       | 
  | acc       | smallint(5) unsigned  | NO   | PRI | 0       |       | 
  +-----------+-----------------------+------+-----+---------+-------+
  </code></pre>
  
  <p>I am expecting duplicates in my data so I went ahead and added a unique constraint:</p>
  
  <pre><code>ALTER TABLE nt_accs ADD UNIQUE(hash,nums,acc);
  </code></pre>
  
  <p>I have about 500 million rows to insert into this table and this table has been paritioned using a RANGE on nums into about 20 partitions. </p>
  
  <ol>
  <li>Does the unique constraint slow down inserts? How does this differ in just making both a Primary Key instead of imposing a unique constraint?</li>
  <li>I have a lot of <code>GROUP BY</code> type queries using both the hash and nums columns. Do I go ahead and add a <a href="https://stackoverflow.com/questions/3849035/what-exactly-happened-with-the-indexing-operation">convering index</a> on  and  or do I just add individual indexes?</li>
  </ol>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Explain plan after partitioning and inserting some test data</p>
  
  <pre><code>1. mysql&gt; explain partitions select * from nt_accs;
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
  | id | select_type | table     | partitions                                                                | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
  |  1 | SIMPLE      | nt_accs   | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | index | NULL          | hash     | 7       | NULL |   10 | Using index | 
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
  1 row in set (0.00 sec)
  
  
  
  2. mysql&gt; explain partitions select * from nt_accs WHERE nums=1504887570;
  +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+--------------------------+
  | id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
  +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+--------------------------+
  |  1 | SIMPLE      | nt_accs   | p7         | index | NULL          | hash     | 7       | NULL |   10 | Using where; Using index | 
  +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+--------------------------+
  1 row in set (0.00 sec)
  
  3. mysql&gt; explain partitions select * from nt_accs WHERE hash=2347200;
  +----+-------------+-----------+---------------------------------------------------------------------------+------+---------------+----------+---------+-------+------+-------------+
  | id | select_type | table     | partitions                                                                | type | possible_keys | key      | key_len | ref   | rows | Extra       |
  +----+-------------+-----------+---------------------------------------------------------------------------+------+---------------+----------+---------+-------+------+-------------+
  |  1 | SIMPLE      | nt_accs  | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | ref  | hash          | hash     | 3       | const |   27 | Using index | 
  +----+-------------+-----------+---------------------------------------------------------------------------+------+---------------+----------+---------+-------+------+-------------+
  1 row in set (0.00 sec)
  
  4. mysql&gt; EXPLAIN PARTITIONS SELECT hash, count(distinct nums) FROM nt_accs GROUP BY hash;
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
  | id | select_type | table     | partitions                                                                | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
  |  1 | SIMPLE      | nt_accs   | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | index | NULL          | hash     | 7       | NULL |   10 | Using index | 
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
  1 row in set (0.00 sec)
  
  5. mysql&gt; EXPLAIN PARTITIONS SELECT nums, count(distinct hash) FROM nt_accs GROUP BY nums;
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-----------------------------+
  | id | select_type | table     | partitions                                                                | type  | possible_keys | key      | key_len | ref  | rows | Extra                       |
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-----------------------------+
  |  1 | SIMPLE      | nt_accs   | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | index | NULL          | hash     | 7       | NULL |   10 | Using index; Using filesort | 
  +----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-----------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>I am perfectly fine with the first and second queries but I'm not sure about the performance of the 3rd, 4th and 5th. Is there anything else I can do at this point to optimize this?</p>