title: MySQL: Why does an Order By ID runs much slower than Order By other Columns?
body: <p>I am using MySQL version 5.5.14 to run the following query, <strong>QUERY 1</strong>, from a table of 5 Million rows:</p>
  
  <pre><code>SELECT P.ID, P.Type, P.Name, P.cty
       , X(P.latlng) as 'lat', Y(P.latlng) as 'lng'
       , P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN
       , P.lv, P.bd, P.bt, P.nb
       , P.ak * E.usD as 'usP' 
  FROM PIG P 
    INNER JOIN EEL E 
      ON E.cur = P.cur 
  WHERE act='1' 
    AND flA &gt;= '1615' 
    AND ldA &gt;= '0' 
    AND yr &gt;= (YEAR(NOW()) - 100) 
    AND lv &gt;= '0' 
    AND bd &gt;= '3' 
    AND bt &gt;= '2' 
    AND nb &lt;= '5' 
    AND cDate &gt;= NOW() 
    AND MBRContains(LineString( Point(39.9097, -2.1973)
                              , Point(65.5130, 41.7480)
                              ), latlng) 
    AND Type = 'g' 
    AND tn = 'l' 
    AND St + Tm - YEAR(NOW()) &gt;= '30' 
  HAVING usP BETWEEN 300/2 AND 300 
  ORDER BY ak
  LIMIT 100;
  </code></pre>
  
  <p>Using an <strong>Index (Type, tn, act, flA)</strong>, I am able to obtain results within <strong>800ms</strong>. In <strong>QUERY 2</strong>, I changed the <code>ORDER BY</code> clause to <code>lv</code>, I am also able to obtain results within similar timings. In <strong>QUERY 3</strong>, I changed the <code>ORDER BY</code> clause to <code>ID</code> and the query time slowed dramatically to a full <strong>20s</strong> on an average of 10 trials.</p>
  
  <p>Running the <code>EXPLAIN SELECT</code> statement produces exactly the same query execution plan:</p>
  
  <pre><code>*************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: P
           type: range
  possible_keys: Index
            key: Index
        key_len: 6
            ref: NULL
           rows: 132478
          Extra: Using where; Using filesort
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: E
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 3
            ref: BS.P.cur
           rows: 1
          Extra: 
  </code></pre>
  
  <p>My question is: <strong>why does ordering by ID in QUERY 3 runs so slow compared to the rest?</strong></p>
  
  <p>The partial table definition is as such:</p>
  
  <pre><code>CREATE TABLE `PIG` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `lv` smallint(3) unsigned NOT NULL DEFAULT '0',
    `ak` int(10) unsigned NOT NULL DEFAULT '0',
  
    PRIMARY KEY (`ID`),
    KEY `id_ca` (`cty`,`ak`),
    KEY `Index` (`Type`, `tn`, `act`, `flA`),
  ) ENGINE=MyISAM AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1
  
  CREATE TABLE `EEL` (
    `cur` char(3) NOT NULL,
    `usD` decimal(11,10) NOT NULL,
    PRIMARY KEY (`cur`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>UPDATE: After extensive testing of various <code>ORDER BY</code>s options, I have confirmed that the ID column which happens to be the Primary Key is the only one causing the slow query time.</p>