I have a table with 30 records.
CREATE TABLE lists (
id int(10) unsigned NOT NULL auto_increment,
name char(50) NOT NULL,
PRIMARY KEY (id),
KEY name (name(20))
)
The data in the "name" column is just alphanumeric characters.
The following query shows "Using filesort" in the Extra column of EXPLAIN.
EXPLAIN SELECT id, name
FROM lists
ORDER BY name
id = 1
select_type = SIMPLE
table = lists
type = index
possible_keys = NULL
key = NULL
key_len = NULL
ref = NULL
rows = 30
Extra = Using filesort
As you can see, it's not using index, even though "name" is indexed.
Somebody suggested that adding WHERE on a column with same name as ORDER BY, removes filesort.
Nope. It didn't work. It won't use the index.
EXPLAIN SELECT *
FROM lists
WHERE name != ''
ORDER BY name
id = 1
select_type = SIMPLE
table = lists
type = ALL
possible_keys = name
key = NULL
key_len = NULL
ref = NULL
rows = 30
Extra = Using where; Using filesort
Am thoroughly confused at this point.
So I decided out of the blue to add LIMIT. Even though I don't need LIMIT in the query.
Voila! This time it used the index.
One caveat, LIMIT must be less than total record count, else it won't use the index.
EXPLAIN SELECT *
FROM lists
WHERE name != ''
ORDER BY name
LIMIT 29
id = 1
select_type = SIMPLE
table = lists
type = index
possible_keys = name
key = name
key_len = 50
ref = NULL
rows = 30
Extra = Using where
I tried other permutations with varying results.
But my questions is:
When using ORDER BY on an alphnumeric column, why won't mysql use index, unless I include LIMIT, with a value less than total records?
I.e. for a table with 30 records, the following uses index
EXPLAIN SELECT *
FROM lists
WHERE name != ''
ORDER BY name
LIMIT 29
but the following doesn't
EXPLAIN SELECT *
FROM lists
WHERE name != ''
ORDER BY name
LIMIT 30
