MySQL not using index for ORDER BY

Question

I've a simple MySQL table named 'test' with two columns:

1. Auto incrementing int column called 'id'
2. Varchar(3000) column called 'textcol'

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.

Any pointers to make changes to help use the index for the ORDER by query will be useful to me.

MySQL version as given by "mysql --version' command:

mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2

mysql> CREATE TABLE test (id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), textcol VARCHAR(3000));
Query OK, 0 rows affected (0.05 sec)

mysql> 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> CREATE INDEX textcolindex ON test (textcol);
Query OK, 0 rows affected, 2 warnings (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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> INSERT INTO test (textcol) VALUES ('test1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (textcol) VALUES ('test2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (textcol) VALUES ('test3');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (textcol) VALUES ('test4');
Query OK, 1 row affected (0.00 sec)


mysql> 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> 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)

Solution 1

Since it has to load the entire table to answer the query and sorting 4 elements is cheap, the query optimizer might just be avoiding touching the index. Does it still happen with larger tables?

Note that a varchar(3000) column can't be a covering index because MySQL won't include more than the first 768 or so bytes of a varchar in an index.

If you want the query to only read the index, the index must have every column you're SELECTing for in it. On innodb, that should start working for your two-column table once you make textcol small enough; on MyISAM you'll need to include the primary key column yourself, like CREATE INDEX textcolindex ON test (textcol,id);

Solution 2

Some useful articles on ORDER BY optimisation:

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

http://opsmonkey.blogspot.co.uk/2009/03/mysql-query-optimization-for-order-by.html

As largely discussed, keep the varchar down to 767 and add a key for the order by:

CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
textcol VARCHAR(767),
PRIMARY KEY(id),
KEY orderby (`textcol`)
);

To avoid filesorts if adding extra 'WHERE' parameters, extend the 'orderby' index key using a multiple column index:

CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT, 
tom INT(11) NOT NULL DEFAULT 0,
gerry INT(11) NOT NULL DEFAULT 0,
textcol VARCHAR(767),
PRIMARY KEY(id), 
KEY orderby (`tom`,`gerry`, `textcol`)
);

Also:

INSERT INTO test (tom, gerry, textcol) VALUES (1,2,'test4');
INSERT INTO test (tom, gerry, textcol) VALUES (1,2,'test2');
EXPLAIN SELECT id, textcol FROM test WHERE tom = 1 AND gerry =2 ORDER BY textcol;

Extra: 'Using where; Using Index'

Solution 3

I got the same problem. MySQL is stupid. fyi: I have a table with more than 500,000,000 records. I wanted to to:

select * from table order by tid limit 10000000, 10;

tid is the primary key in the table and is automatically indexed by mysql.

This took a long time and I cancelled the query. then I let mysql "explain" the query and recognized that it won't use the index for the primary key. after reading many docs from mysql i tried to force mysql to use the index via "USE INDEX(...)" and dis also did not work. Then I rocognized that mysql seems to always correlate the where clause with the order by clause. So I tried to extend the where clause with a condition with touches the index. I ended up with:

select * from table use index (PRIMARY) where tid > 0 order by tid limit 10000000, 10;

where tid is the primary key in the table and is an autoincrement value which starts at 1.

This worked after I let mysql explain the query to me. And behold: the query took only 4 seconds.