title: Optimize Indexes for Particular Query in mySQL
body: <p>I have a fairly simple query that is taking about 14 seconds to complete and I would like to speed it up. I think I have the correct indexes in place, but I'm not sure...</p>
  
  <p><strong>Here is the query</strong></p>
  
  <pre><code>SELECT *
  FROM opportunities
  WHERE cid = 7785
    AND STATUS != 4
    AND otype != 200
    AND links &gt; 0
    AND ontopic != 'F'
  ORDER BY links DESC
  LIMIT 0, 100;
  </code></pre>
  
  <p><strong>Here is the table schema</strong></p>
  
  <pre><code>CREATE TABLE `opportunities` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `cid` int(11) NOT NULL,
    `url` varchar(900) CHARACTER SET utf8 NOT NULL,
    `status` tinyint(4) NOT NULL,
    `links` int(11) NOT NULL,
    `otype` int(11) NOT NULL,
    `reserved` tinyint(4) NOT NULL,
    `ontopic` varchar(3) CHARACTER SET utf8 NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `cid` (`cid`,`url`),
    KEY `cid1` (`cid`),
    KEY `url` (`url`),
    KEY `otype` (`otype`),
    KEY `reserved` (`reserved`),
    KEY `ontopic` (`ontopic`),
    KEY `status` (`status`),
    KEY `links` (`links`),
    KEY `ontopic_links` (`ontopic`,`links`),
    KEY `cid_status_otype_links_ontopic` (`cid`,`status`,`otype`,`links`,`ontopic`)
  ) ENGINE=InnoDB AUTO_INCREMENT=13022832 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p><strong>Here is the result of the EXPLAIN command</strong></p>
  
  <pre><code>id: 1
  select_type: Simple
  table: opportunities
  partitions: null
  type: range
  possible_keys: cid,cid1,otype,ontopic,status,links,ontopic_links,cid_status_otype_links_ontopic
  key: links
  keylen: 4
  ref: null
  rows: 1531552
  filtered: 0.33
  Extra: Using index condition; Using where
  </code></pre>
  
  <p><strong>Thoughts / Questions</strong></p>
  
  <p>Am I reading it correctly that it is using the "links" key to do the query? Why wouldn't it use a more complete index, like the cid_status_otype_links_ontopic which covers all the conditions of my query?</p>
  
  <p>Thanks in advance!</p>
  
  <p><strong>As requested</strong></p>
  
  <p>There are 30,961 results that match the query when you remove the LIMIT 0,100. Interestingly, the "count()" command returns almost instantaneously.</p>