DeutscheMark
New Member
I've a simple MySQL table named 'test' with two columns:[*]Auto incrementing int column called 'id'[*]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\[code\]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: 0mysql> 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)\[/code\]