title: MySQL using different index depending on limit value with ORDER BY query
body: <p>This is weird to me:
  One table 'ACTIVITIES' with one index on ACTIVITY_DATE. The exact same query with different LIMIT value results in different execution plan.</p>
  
  <p>Here it is:</p>
  
  <pre><code>mysql&gt; explain select * from ACTIVITIES order by ACTIVITY_DATE desc limit 20
      -&gt; ;
  +----+-------------+------------+-------+---------------+-------------+---------+------+------+-------+
  | id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows | Extra |
  +----+-------------+------------+-------+---------------+-------------+---------+------+------+-------+
  |  1 | SIMPLE      | ACTIVITIES | index | NULL          | ACTI_DATE_I | 4       | NULL |   20 |       |
  +----+-------------+------------+-------+---------------+-------------+---------+------+------+-------+
  1 row in set (0.00 sec)
  
  mysql&gt; explain select * from ACTIVITIES order by ACTIVITY_DATE desc limit 150
      -&gt; ;
  +----+-------------+------------+------+---------------+------+---------+------+-------+----------------+
  | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
  +----+-------------+------------+------+---------------+------+---------+------+-------+----------------+
  |  1 | SIMPLE      | ACTIVITIES | ALL  | NULL          | NULL | NULL    | NULL | 10629 | Using filesort |
  +----+-------------+------------+------+---------------+------+---------+------+-------+----------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>How come when I limit 150 it is not using the index? I mean, scanning 150 lines seems faster than scanning 10629 rows, right?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>The query uses the index till "limit 96" and starts filesort at "limit 97".
  The table has nothing specific, even not a foreign key, here is the complete create table:</p>
  
  <pre><code>mysql&gt; show create table ACTIVITIES\G
  *************************** 1. row ***************************
         Table: ACTIVITIES
  Create Table: CREATE TABLE `ACTIVITIES` (
    `ACTIVITY_ID` int(11) NOT NULL AUTO_INCREMENT,
    `ACTIVITY_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `USER_KEY` varchar(50) NOT NULL,
    `ITEM_KEY` varchar(50) NOT NULL,
    `ACTIVITY_TYPE` varchar(1) NOT NULL,
    `EXTRA` varchar(500) DEFAULT NULL,
    `IS_VISIBLE` varchar(1) NOT NULL DEFAULT 'Y',
    PRIMARY KEY (`ACTIVITY_ID`),
    KEY `ACTI_USER_I` (`USER_KEY`,`ACTIVITY_DATE`),
    KEY `ACTIVITY_ITEM_I` (`ITEM_KEY`,`ACTIVITY_DATE`),
    KEY `ACTI_ITEM_TYPE_I` (`ITEM_KEY`,`ACTIVITY_TYPE`,`ACTIVITY_DATE`),
    KEY `ACTI_DATE_I` (`ACTIVITY_DATE`)
  ) ENGINE=InnoDB AUTO_INCREMENT=10091 DEFAULT CHARSET=utf8 COMMENT='Logs    activity'
  1 row in set (0.00 sec)
  
  mysql&gt; 
  </code></pre>
  
  <p>I also tried to run "ANALYSE TABLE ACTIVITIES" but that did not change a thing.</p>