Description:
MySQL doesn't use indexes which was created with index prefix length using col_name(length) syntax to resolve the ORDER BY
How to repeat:
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id int(11) unsigned NOT NULL auto_increment,
title varchar(255) NOT NULL default '',
PRIMARY KEY (id),
INDEX title (title(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO products (title) VALUES ('9 Product');
INSERT INTO products (title) VALUES ('8 Product');
INSERT INTO products (title) VALUES ('7 Product');
INSERT INTO products (title) VALUES ('6 Product');
INSERT INTO products (title) VALUES ('5 Product');
INSERT INTO products (title) VALUES ('4 Product');
INSERT INTO products (title) VALUES ('3 Product');
INSERT INTO products (title) VALUES ('2 Product');
INSERT INTO products (title) VALUES ('1 Product');
INSERT INTO products (title) VALUES ('0 Product');
mysql> EXPLAIN SELECT id, title FROM products ORDER BY title LIMIT 5;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
If you use an index created from the whole field everything works fine:
ALTER TABLE products DROP KEY title;
ALTER TABLE products ADD KEY title (title);
mysql> EXPLAIN SELECT id, title FROM products ORDER BY title LIMIT 5;
+----+-------------+----------+-------+---------------+-------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-------+---------+------+------+-------+
| 1 | SIMPLE | products | index | NULL | title | 765 | NULL | 10 | |
+----+-------------+----------+-------+---------------+-------+---------+------+------+-------+
1 row in set (0.01 sec)
Description: MySQL doesn't use indexes which was created with index prefix length using col_name(length) syntax to resolve the ORDER BY How to repeat: DROP TABLE IF EXISTS products; CREATE TABLE products ( id int(11) unsigned NOT NULL auto_increment, title varchar(255) NOT NULL default '', PRIMARY KEY (id), INDEX title (title(10)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO products (title) VALUES ('9 Product'); INSERT INTO products (title) VALUES ('8 Product'); INSERT INTO products (title) VALUES ('7 Product'); INSERT INTO products (title) VALUES ('6 Product'); INSERT INTO products (title) VALUES ('5 Product'); INSERT INTO products (title) VALUES ('4 Product'); INSERT INTO products (title) VALUES ('3 Product'); INSERT INTO products (title) VALUES ('2 Product'); INSERT INTO products (title) VALUES ('1 Product'); INSERT INTO products (title) VALUES ('0 Product'); mysql> EXPLAIN SELECT id, title FROM products ORDER BY title LIMIT 5; +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort | +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) If you use an index created from the whole field everything works fine: ALTER TABLE products DROP KEY title; ALTER TABLE products ADD KEY title (title); mysql> EXPLAIN SELECT id, title FROM products ORDER BY title LIMIT 5; +----+-------------+----------+-------+---------------+-------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+-------+---------+------+------+-------+ | 1 | SIMPLE | products | index | NULL | title | 765 | NULL | 10 | | +----+-------------+----------+-------+---------------+-------+---------+------+------+-------+ 1 row in set (0.01 sec)