title: MySQL select distinct query not using index
body: <p>I have a table <code>clicks</code>:</p>
  
  <pre><code>CREATE TABLE `clicks` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `link_id` int(11) NOT NULL,
    `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    PRIMARY KEY (`id`),
    KEY `link_id` (`link_id`),
    KEY `date_added` (`date_added`)
  ) ENGINE=InnoDB AUTO_INCREMENT=90899051 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>
  
  <p>With the following indexes:</p>
  
  <pre><code>+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | clicks |          0 | PRIMARY    |            1 | id          | A         |    79808649 |     NULL | NULL   |      | BTREE      |         |               |
  | clicks |          1 | link_id    |            1 | link_id     | A         |      276154 |     NULL | NULL   |      | BTREE      |         |               |
  | clicks |          1 | date_added |            1 | date_added  | A         |    79808649 |     NULL | NULL   |      | BTREE      |         |               |
  +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>I'm trying to run some analytical queries on this table and I'm finding that it's taking ages to run. Take the following query for example:</p>
  
  <pre><code>SELECT
      DISTINCT(link_id) AS link_id
  FROM
      clicks
  WHERE
      date_added &gt;= '2016-11-01 00:00:00'
  AND date_added &lt;= '2016-12-05 10:16:00'
  </code></pre>
  
  <p>This query took almost a minute to complete. I found by running an <code>EXPLAIN</code> on the query that the indexes weren't used.</p>
  
  <pre><code>+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
  | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
  +----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
  |  1 | SIMPLE      | clicks | index | date_added    | link_id | 4       | NULL | 79786609 | Using where |
  +----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
  </code></pre>
  
  <p>I expected that the query would have been run by using the index on the <code>date_added</code> column to filter down the result set before then pulling out the distinct <code>link_id</code>s from the result.</p>
  
  <p>Does anyone know why the index is not being used or if there's anything I can do to force it to be used?</p>
  
  <p>Note: This question is part of a bigger issue and is closely related to an unsolved issue I posted last week - <a href="https://stackoverflow.com/questions/40933842/mysql-query-with-join-not-using-index">MySQL query with JOIN not using INDEX</a></p>
  
  <hr>
  
  <p><strong>Edit</strong></p>
  
  <p>Explain on my query, without using any index hints:</p>
  
  <pre><code>EXPLAIN SELECT DISTINCT(link_id) FROM clicks WHERE date_added &gt;= '2016-11-01 00:00:00' AND date_added &lt;= '2016-12-05 23:59:59';
  +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+-------------+
  | id | select_type | table                     | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
  +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+-------------+
  |  1 | SIMPLE      | clicks                    | index | date_added    | link_id | 4       | NULL | 79816660 | Using where |
  +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+-------------+
  </code></pre>
  
  <p>Explain on my query WITH index hints:</p>
  
  <pre><code>EXPLAIN SELECT DISTINCT(link_id) FROM clicks USE INDEX(date_added) IGNORE INDEX(link_id) WHERE date_added &gt;= '2016-11-01 00:00:00' AND date_added &lt;= '2016-12-05 23:59:59';
  +----+-------------+---------------------------+------+---------------+------+---------+------+----------+------------------------------+
  | id | select_type | table                     | type | possible_keys | key  | key_len | ref  | rows     | Extra                        |
  +----+-------------+---------------------------+------+---------------+------+---------+------+----------+------------------------------+
  |  1 | SIMPLE      | clicks                    | ALL  | date_added    | NULL | NULL    | NULL | 79816882 | Using where; Using temporary |
  +----+-------------+---------------------------+------+---------------+------+---------+------+----------+------------------------------+
  </code></pre>
  
  <p><strong>Edit 2</strong></p>
  
  <p>Using <code>FORCE INDEX(date_added)</code> on my query (Query completed even quicker, 12.05 sec):</p>
  
  <pre><code>EXPLAIN SELECT DISTINCT(link_id) FROM clicks FORCE INDEX(date_added) WHERE date_added &gt;= '2016-11-01 00:00:00' AND date_added &lt;= '2016-12-05 23:59:59';
  +----+-------------+---------------------------+-------+---------------+------------+---------+------+----------+------------------------------+
  | id | select_type | table                     | type  | possible_keys | key        | key_len | ref  | rows     | Extra                        |
  +----+-------------+---------------------------+-------+---------------+------------+---------+------+----------+------------------------------+
  |  1 | SIMPLE      | clicks                    | range | date_added    | date_added | 4       | NULL | 17277508 | Using where; Using temporary |
  +----+-------------+---------------------------+-------+---------------+------------+---------+------+----------+------------------------------+
  </code></pre>