title: Simple query optimization (WHERE + ORDER + LIMIT)
body: <p>I have this query that runs unbelievably slow (4 minutes):</p>
  
  <pre><code>SELECT * FROM `ad` WHERE `ad`.`user_id` = USER_ID ORDER BY `ad`.`id` desc LIMIT 20;
  </code></pre>
  
  <p>Ad table has approximately 10 million rows. </p>
  
  <pre><code>SELECT COUNT(*) FROM `ad` WHERE `ad`.`user_id` = USER_ID;
  </code></pre>
  
  <p>Returns 10k rows.</p>
  
  <p>Table has following indexes:</p>
  
  <pre><code>  PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`,`status`,`sorttime`),
  </code></pre>
  
  <p>EXPLAIN gives this:</p>
  
  <pre><code>           id: 1
    select_type: SIMPLE
          table: ad
           type: index
  possible_keys: idx_user_id
            key: PRIMARY
        key_len: 4
            ref: NULL
           rows: 4249
          Extra: Using where
  </code></pre>
  
  <p>I am failing to understand why does it take so long? Also this query is generated by ORM (pagination) so it would be nice to optimize it from outside (maybe add some extra index).</p>
  
  <p>BTW this query works fast:</p>
  
  <pre><code>select aa.*
  from (select id from ad where user_id=USER_ID order by id desc limit 20) as a
  join ad as aa on a.id = aa.id ;
  </code></pre>
  
  <p><strong>Edit:</strong> I tried another user with much less rows (dozens) than original one. I am wondering why doesn't original query use <code>idx_user_id</code>:</p>
  
  <pre><code>EXPLAIN SELECT * FROM `ad` WHERE `ad`.`user_id` = ANOTHER_ID ORDER BY `ad`.`id` desc LIMIT 20;
  
             id: 1
    select_type: SIMPLE
          table: ad
           type: ref
  possible_keys: idx_user_id
            **key: idx_user_id**
        key_len: 3
            ref: const
           rows: 84
          Extra: Using where; Using filesort
  </code></pre>
  
  <p><strong>Edit2:</strong> with help of Alexander I decided to try force MySQL to use the index I want, and following query is much faster (1 sec instead of 4 mins):</p>
  
  <pre><code>SELECT * 
  FROM `ad` USE INDEX (idx_user_id)
  WHERE `ad`.`user_id` = 1884774
  ORDER BY `ad`.`id` desc LIMIT 20; 
  </code></pre>