title: How wrong is it to have a unique and normal index on the same column?
body: <p>I have the following table structure</p>
  
  <pre><code>CREATE TABLE `table` (
    `id` int(11) NOT NULL auto_increment,
    `date_expired` datetime NOT NULL,
    `user_id` int(11) NOT NULL,
    `foreign_id` int(11) NOT NULL,
    PRIMARY KEY  (`id`),
    UNIQUE KEY `date_expired` (`date_expired`,`user_id`,`foreign_id`),
    KEY `user_id` (`user_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>As you'll notice, I have duplicate indexes on user_id: <code>date_expired</code> &amp; <code>user_id</code>. I of course want the unique index because I want to ensure the data is unique.</p>
  
  <p>The reason for the duplicate indexes is because without the <code>user_id</code> index, my main search query takes 4 seconds. With the extra index it takes 1 second. The query is joining the table on <code>user_id</code> and checking <code>date_expired</code>.</p>
  
  <p>The table only has 275 records.</p>
  
  <ul>
  <li>How bad is it to have a unique and normal index on the same field?</li>
  <li>How bad is it to have larger indexes than data when the table is purely ids?</li>
  </ul>