title: Why Does MySQL not use an index?
body: <p>Our MySQL(Percona Server) database have a big table cotains 10 millions rows, there are many slow query longger than 40 seconds like this:</p>
  
  <pre><code>  SELECT col1, Seller, col3, col4, Id, col5  
      FROM table1 
     WHERE Seller = 346761 
       AND col1 IN (2, 3, 4) 
       AND col3 = 1  
       AND col4 NOT IN (5,6,7)  
  ORDER BY Id DESC 
     LIMIT 0, 20;
  </code></pre>
  
  <p>I had create index on <code>Seller</code>, and <code>col1</code>, <code>col3</code>, <code>col4</code>. These indexes are individual, not Multiple-Column Index (AKA covering index). And the <code>Id</code> is primary key.</p>
  
  <p>EXPLAIN show that MySQL query this sql using Primary key as index, not index about Seller.</p>
  
  <pre><code>+----+-------------+------------------+-------+--------------------+---------+---------+------+------+-------------+
  | id | select_type | table            | type  | possible_keys      | key     | key_len | ref  | rows | Extra       |
  +----+-------------+------------------+-------+--------------------+---------+---------+------+------+-------------+
  |  1 | SIMPLE      | trans_audit_list | index | Seller,AuditStatus | PRIMARY | 8       | NULL | 1483 | Using where | 
  +----+-------------+------------------+-------+--------------------+---------+---------+------+------+-------------+
  </code></pre>
  
  <p>When I <code>force index (Seller)</code>, it's very fast, 0.7 second.</p>
  
  <p>And I found if not use limit statement, this query will use <code>Seller</code> index, will be very fast.
  Why MySQL not using index on <code>Seller</code> with limit statement?</p>