title: MySQL query with JOIN not using INDEX
body: <p>I have the following two tables in MySQL (Simplified).</p>
  
  <ul>
  <li><code>clicks</code> (InnoDB)
  
  <ul>
  <li>Contains around about 70,000,000 records</li>
  <li>Has an index on the <code>date_added</code> column</li>
  <li>Has a column <code>link_id</code> which refers to a record in the <code>links</code> table</li>
  </ul></li>
  <li><code>links</code> (MyISAM)
  
  <ul>
  <li>Contains far fewer records, around about 65,000</li>
  </ul></li>
  </ul>
  
  <p>I'm trying to run some analytical queries using these tables. I need to pull out some data, about clicks that occurred inside of two specified dates while applying some other user selected filters using other tables and joining them into the links table.</p>
  
  <p>My question revolves around the use of indexes however. When I run the following query:</p>
  
  <pre><code>SELECT
      COUNT(1)
  FROM
      clicks
  WHERE
      date_added &gt;= '2016-11-01 00:00:00'
  AND date_added &lt;= '2016-11-03 23:59:59';
  </code></pre>
  
  <p>I get a response back in 1.40 sec. Using <code>EXPLAIN</code> I find that the MySQL uses the index on the <code>date_added</code> column as expected.</p>
  
  <pre><code>EXPLAIN SELECT COUNT(1) FROM clicks WHERE date_added &gt;= '2016-11-01 00:00:00' AND date_added &lt;= '2016-11-16 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 | 1559288 | Using where; Using index |
  +----+-------------+--------+-------+---------------+------------+---------+------+---------+--------------------------+
  </code></pre>
  
  <p>However, when I <code>LEFT JOIN</code> in my <code>links</code> table I find that the query takes much longer to execute:</p>
  
  <pre><code>SELECT
      COUNT(1) AS clicks
  FROM
      clicks AS c
  LEFT JOIN links AS l ON l.id = c.link_id
  WHERE
      c.date_added &gt;= '2016-11-01 00:00:00'
  AND c.date_added &lt;= '2016-11-16 23:59:59';
  </code></pre>
  
  <p>Which completed in 6.50 sec. Using <code>EXPLAIN</code> I find that the index was not used on the <code>date_added</code> column:</p>
  
  <pre><code>EXPLAIN SELECT COUNT(1) AS clicks FROM clicks AS c LEFT JOIN links AS l ON l.id = c.link_id WHERE c.date_added &gt;= '2016-11-01 00:00:00' AND c.date_added &lt;= '2016-11-16 23:59:59';
  +----+-------------+-------+--------+---------------+------------+---------+---------------+---------+-------------+
  | id | select_type | table | type   | possible_keys | key        | key_len | ref           | rows    | Extra       |
  +----+-------------+-------+--------+---------------+------------+---------+---------------+---------+-------------+
  |  1 | SIMPLE      | c     | range  | date_added    | date_added | 4       | NULL          | 6613278 | Using where |
  |  1 | SIMPLE      | l     | eq_ref | PRIMARY       | PRIMARY    | 4       | c.link_id     |       1 | Using index |
  +----+-------------+-------+--------+---------------+------------+---------+---------------+---------+-------------+
  </code></pre>
  
  <p>As you can see the index isn't being used for the <code>date_added</code> column in the larger table and seems to take far longer. This seems to get even worse when I join in other tables.</p>
  
  <p>Does anyone know why this is happening or if there's anything I can do to get it to use the index on the <code>date_added</code> column in the clicks table?</p>
  
  <hr>
  
  <p><strong>Edit</strong></p>
  
  <p>I've just attempted to get my stats out of the database using a different method. The first step in my method involves pulling out a distinct set of <code>link_id</code>s from the clicks table. I've found that I'm seeing the same problem here again, without a JOIN. The index is not being used:</p>
  
  <p>My query:</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 ran an <code>EXPLAIN</code> on this and found that the query is not using the index as I expected it would:</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 it would use the index on <code>date_added</code> to filter down the result set and then pull out the distinct <code>link_id</code> values. Any idea why this is happening? I have an index on <code>link_id</code> as well as <code>date_added</code>.</p>