title: mysql difference in number of rows scanned
body: <p>I have a query :</p>
  
  <pre><code>mysql&gt; explain SELECT  *
      FROM  OTHERS_TINY_URL_TBL
      WHERE  LINK_TYPE = 'BITLY'
        AND  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
        AND  MODIFIED_TM &gt;= '2016-03-13 21:09:43'
        AND  MODIFIED_TM &lt;= '2017-03-13 21:09:43'
        AND  POST_ID &lt; 0
      ORDER BY  MODIFIED_TM DESC
      LIMIT  1000\G;
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: OTHERS_TINY_URL_TBL
           type: range
  possible_keys: XIE1_OTHERS_TINY_URL_TBL,XIE2_OTHERS_TINY_URL_TBL,XIE5_OTHERS_TINY_URL_TBL
            key: XIE5_OTHERS_TINY_URL_TBL
        key_len: 4
            ref: NULL
           rows: 47168
          Extra: Using index condition; Using where
  1 row in set (0.00 sec)
  
  ERROR: 
  No query specified
  </code></pre>
  
  <p>I don't get why it is scanning so many rows. It seems ORDER BY and LIMIT is making it inefficient</p>
  
  <p>Without ORDER BY and LIMIT:</p>
  
  <pre><code>mysql&gt; explain SELECT  *
      FROM  OTHERS_TINY_URL_TBL
      WHERE  LINK_TYPE = 'BITLY'
        AND  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
        AND  MODIFIED_TM &gt;= '2016-03-13 21:09:43'
        AND  MODIFIED_TM &lt;= '2017-03-13 21:09:43'
        AND  POST_ID &lt; 0\G;
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: OTHERS_TINY_URL_TBL
           type: range
  possible_keys: XIE1_OTHERS_TINY_URL_TBL,XIE2_OTHERS_TINY_URL_TBL,XIE5_OTHERS_TINY_URL_TBL
            key: XIE2_OTHERS_TINY_URL_TBL
        key_len: 9
            ref: NULL
           rows: 4950
          Extra: Using index condition; Using where
  1 row in set (0.00 sec)
  
  ERROR: 
  No query specified
  </code></pre>
  
  <p>Only 85 rows in the filtered set:</p>
  
  <pre><code>mysql&gt;  SELECT  count(*)
      FROM  OTHERS_TINY_URL_TBL
      WHERE  LINK_TYPE = 'BITLY'
        AND  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
        AND  MODIFIED_TM &gt;= '2016-03-13 21:09:43'
        AND  MODIFIED_TM &lt;= '2017-03-13 21:09:43'
        AND  POST_ID &lt; 0\G;
  *************************** 1. row ***************************
  count(*): 85
  1 row in set (0.02 sec)
  
  ERROR: 
  No query specified
  </code></pre>
  
  <p>Creating a nested query scanned on lesser number of rows:</p>
  
  <pre><code>mysql&gt; explain SELECT  *
      FROM  
      (
          SELECT  *
              from  OTHERS_TINY_URL_TBL
              where  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
                AND  MODIFIED_TM &gt;= '2016-03-13 21:09:43'
                AND  MODIFIED_TM &lt;= '2017-03-13 21:09:43'
                AND  POST_ID &lt; 0
      ) inner_t
      where  true
      ORDER BY  MODIFIED_TM DESC
      LIMIT  1000\G;
  *************************** 1. row ***************************
             id: 1
    select_type: PRIMARY
          table: &lt;derived2&gt;
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 4950
          Extra: Using filesort
  *************************** 2. row ***************************
             id: 2
    select_type: DERIVED
          table: OTHERS_TINY_URL_TBL
           type: range
  possible_keys: XIE2_OTHERS_TINY_URL_TBL,XIE5_OTHERS_TINY_URL_TBL
            key: XIE2_OTHERS_TINY_URL_TBL
        key_len: 9
            ref: NULL
           rows: 4950
          Extra: Using index condition; Using where
  2 rows in set (0.00 sec)
  
  ERROR: 
  No query specified
  </code></pre>
  
  <p>Why is the first query inefficient?</p>