title: MySQL not using index for ORDER BY
body: <p>I've a simple MySQL table named 'test' with two columns:</p>
  
  <ol>
  <li>Auto incrementing int column called 'id'</li>
  <li>Varchar(3000) column called 'textcol'</li>
  </ol>
  
  <p>I create an index in the table based on the 'textcol' column. However, the ORDER BY query doesn't seem to be using the index i.e. the EXPLAIN statement on a simple query with ORDER BY on textcol shows NULL in the Key column in its output and also uses filesort.</p>
  
  <p>Any pointers to make changes to help use the index for the ORDER by query will be useful to me.</p>
  
  <p>MySQL version as given by "mysql --version' command:</p>
  
  <p><strong>mysql  Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2</strong></p>
  
  <pre><code>mysql&gt; CREATE TABLE test (id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), textcol VARCHAR(3000));
  Query OK, 0 rows affected (0.05 sec)
  
  mysql&gt; DESCRIBE test;
  +---------+---------------+------+-----+---------+----------------+
  | Field   | Type          | Null | Key | Default | Extra          |
  +---------+---------------+------+-----+---------+----------------+
  | id      | int(11)       | NO   | PRI | NULL    | auto_increment |
  | textcol | varchar(3000) | YES  |     | NULL    |                |
  +---------+---------------+------+-----+---------+----------------+
  2 rows in set (0.00 sec)
  
  mysql&gt; CREATE INDEX textcolindex ON test (textcol);
  Query OK, 0 rows affected, 2 warnings (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql&gt; SHOW INDEX FROM test;
  +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | test  |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
  | test  |          1 | textcolindex |            1 | textcol     | A         |        NULL |     1000 | NULL   | YES  | BTREE      |         |
  +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  2 rows in set (0.00 sec)
  
  mysql&gt; INSERT INTO test (textcol) VALUES ('test1');
  Query OK, 1 row affected (0.00 sec)
  
  mysql&gt; INSERT INTO test (textcol) VALUES ('test2');
  Query OK, 1 row affected (0.00 sec)
  
  mysql&gt; INSERT INTO test (textcol) VALUES ('test3');
  Query OK, 1 row affected (0.00 sec)
  
  mysql&gt; INSERT INTO test (textcol) VALUES ('test4');
  Query OK, 1 row affected (0.00 sec)
  
  
  mysql&gt; EXPLAIN SELECT * FROM test ORDER BY textcol;
  +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  |  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort |
  +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  1 row in set (0.00 sec)
  
  mysql&gt; EXPLAIN SELECT * FROM test ORDER BY id;
  +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  |  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort |
  +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  1 row in set (0.00 sec)
  </code></pre>