title: mysql process list: "Creating sort index" or "sending data" very slow on indexed query
body: <p>I'm on mysql 5.6, innoDB, here is the query:</p>
  
  <pre><code>SELECT * 
  FROM activities use index (user_id_start_time_end_time) 
  WHERE user_id=219439 
    AND end_time &lt;= 1414524849 
    AND start_time &gt;= 1413867600 
  ORDER BY end_time DESC 
  LIMIT 1000
  </code></pre>
  
  <p>This started happening after an upgrade from 5.5, and it takes about 5-10 seconds to complete. th<code>user_id_start_time_end_time</code> is a compound index on user_id, start_time and end_time.</p>
  
  <p>If if explain the query, I get : </p>
  
  <p><code>Using index condition; Using filesort</code> in the extra column and a reasonable amount of rows (~500). </p>
  
  <p>If I take out the <code>ORDER BY end_time DESC LIMIT 1000</code> it's actually worse, the query gets stuck in <code>sending data</code> state, and completes in >10 seconds.</p>
  
  <p>The table has about 150M rows.</p>
  
  <p>EDIT1:</p>
  
  <p><code>SHOW CREATE TABLE</code> statement</p>
  
  <pre><code>CREATE TABLE `activities` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `start_time` int(11) NOT NULL,
    `end_time` int(11) NOT NULL,
    `user_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `start_time` (`start_time`),
    KEY `end_time` (`end_time`),
    KEY `user_id` (`user_id`),
    KEY `user_id_start_time_end_time` (`user_id`,`start_time`,`end_time`)
  ) ENGINE=InnoDB AUTO_INCREMENT=193763922 DEFAULT CHARSET=utf8 
  </code></pre>
  
  <p>(slightly edited).</p>
  
  <p>7.5 GB ram (on AWS RDS). </p>
  
  <pre><code>BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 5839126528; in additional pool allocated 0
  Dictionary memory allocated 374910
  Buffer pool size   348224
  Free buffers       7784
  Database pages     318670
  Old database pages 117550
  Modified db pages  51295
  Pending reads 18
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 8233948, not young 97502110
  267.01 youngs/s, 1694.11 non-youngs/s
  Pages read 11060592, created 116307, written 5005689
  285.42 reads/s, 2.67 creates/s, 63.31 writes/s
  Buffer pool hit rate 992 / 1000, young-making rate 7 / 1000 not 51 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 318670, unzip_LRU len: 0
  I/O sum[144032]:cur[750], unzip sum[0]:cur[0]
  </code></pre>