title: Why is MySQL not using my index ? Optimizing mysql select query
body: <p>I am trying to optimize a MySQL select request:</p>
  
  <pre><code>SELECT * FROM `sales` 
  WHERE ((sales.private = false AND (sales.buyer_id IS NULL OR NOT sales.buyer_id=142)
    AND (sales.merchand_id IS NULL OR NOT sales.merchand_id=142)
    AND (sales.private_item = false) )
    AND ((sales.buyer_id=32 OR sales.merchand_id=32)
    AND (sales.admin=0 AND NOT sales.type IN ('book'))))
  ORDER BY sales.created_at DESC, sales.id DESC LIMIT 0, 10;
  </code></pre>
  
  <p>The schema of the table is </p>
  
  <pre><code>mysql&gt; SHOW columns from sales;
  +------------------------+--------------+------+-----+---------+----------------+
  | Field                  | Type         | Null | Key | Default | Extra          |
  +------------------------+--------------+------+-----+---------+----------------+
  | id                     | int(11)      | NO   | PRI | NULL    | auto_increment |
  | type                   | varchar(255) | YES  | MUL | NULL    |                |
  | buyer_id               | int(11)      | YES  | MUL | NULL    |                |
  | merchand_id            | int(11)      | YES  | MUL | NULL    |                |
  | private                | tinyint(1)   | YES  |     | 0       |                |
  | admin                  | tinyint(1)   | YES  |     | 0       |                |
  | created_at             | datetime     | YES  |     | NULL    |                |
  | updated_at             | datetime     | YES  |     | NULL    |                |
  | country_id             | int(11)      | YES  | MUL | 0       |                |
  | private_item           | tinyint(1)   | YES  |     | 0       |                |
  +------------------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>The indexes are:</p>
  
  <pre><code>    mysql&gt; show indexes from sales;
  +-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table | Non_unique | Key_name                           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | sales |          0 | PRIMARY                            |            1 | id          | A         |      286509 |     NULL | NULL   |      | BTREE      |         |
  | sales |          1 | index_sales_on_type                |            1 | type        | A         |         123 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_on_buyer_id            |            1 | buyer_id    | A         |       40929 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_on_merchand_id         |            1 | merchand_id | A         |       40929 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_on_country_id          |            1 | country_id  | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_on_type_and_country_id |            1 | type        | A         |         151 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_on_type_and_country_id |            2 | country_id  | A         |         428 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            1 | buyer_id    | A         |       35813 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            2 | merchand_id | A         |      286509 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            3 | private_item| A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            4 | admin       | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            5 | type        | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            6 | private     | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
  | sales |          1 | index_sales_viewed                 |            7 | created_at  | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
  +-------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  </code></pre>
  
  <p>When doing the query it uses the <strong>index_sales_on_type_and_country_id</strong> even if there is no country_id in the query...</p>
  
  <p>The query takes 2.5 seconds with this index.</p>
  
  <p>But when I use <strong>USE INDEX(index_sales_viewed)</strong> it goes down to 0.2 seconds.</p>
  
  <p>Here is the EXPLAIN of the query:</p>
  
  <pre><code>+----+-------------+-----------------+------+----------------------------------------------+------+---------+------+--------+---------------------
  | id | select_type | table | type  | possible_keys  | key                                | key_len | ref  | rows   | Extra                       | 
  +----+-------------+-----------------+------+----------------------------------------------+------+---------+------+--------+---------------------
  |  1 | SIMPLE      | sales | range |  see bellow    | index_sales_on_type_and_country_id | 258     | NULL | 208725 | Using where; Using filesort | 
  +----+-------------+-----------------+------+----------------------------------------------+------+---------+------+--------+---------------------
  </code></pre>
  
  <p>the possible keys are :</p>
  
  <pre><code>index_sales_on_type,
  index_sales_on_buyer_id,
  index_sales_on_merchand_id,
  index_sales_on_type_and_country_id,
  index_sales_public_recent_activity
  </code></pre>
  
  <p>Why doesn't MySQL use <strong>index_sales_viewed</strong> by default? Could there be a better index?</p>
  
  <p>Thank you!</p>