title: Why am I getting filesort on this really simple MySQL query?
body: <p>Table has two columns:</p>
  
  <pre><code>CREATE TABLE items ( 
      k INT auto_increment PRIMARY KEY, 
      val INT UNSIGNED
  ) ENGINE=MyISAM;
  </code></pre>
  
  <p>I put four items in the table:</p>
  
  <pre><code>INSERT INTO items (val) VALUES (12),(23),(45),(56);
  </code></pre>
  
  <p>Now if I do:</p>
  
  <pre><code>EXPLAIN SELECT * FROM items ORDER BY k;
  </code></pre>
  
  <p>I get the dreaded "using filesort". What's going on? According to <a href="http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html" rel="nofollow noreferrer">this page</a>, it should be using the index for the ordering. Am I doing something wrong?</p>
  
  <p>Tested on MySQL 5.0.41 on XP and 5.0.67 on ubuntu.</p>
  
  <p><strong>UPDATE</strong>: I added 1,110,000 rows to the table, I added a VARCHAR column and filled it with text. The table size is now 135MB, and I'm still getting "using filesort". </p>
  
  <ol>
  <li>Anyone has any tips as to how to add lots of rows quickly?</li>
  <li>At what point should I consider a table "large enough" for query optimization testing?</li>
  </ol>