Why is my MySQL SELECT statement with ORDER BY so slow even though the INDEX is on the column?
I have a table movies
. It has 1.3 million lines.
In a column, the title
table has INDEX
, order asc
, length 255
.
The column title
represents VARCHAR(1000)
.
Even with this setting, the next request takes 8 seconds to run. Any ideas or shots in the dark why this might be? I'm stumped because it seems such a basic problem.
SELECT title
FROM movies
ORDER BY title
LIMIT 150000, 50000
When I take out ORDER BY
, the query is very fast (0.05 seconds):
SELECT title
FROM movies
LIMIT 150000, 50000
source to share
Edit: The index prefix is a better name than the partial index I used.
Since your index is a partial index, MySQL may not use it for ordering, and still must sort the values by their full length.
Try this small sample:
create table o1 (a varchar(10));
insert into o1 values('test1'),('test2'),('test3'),('tes1');
create index oindex on o1 (a);
explain select a from o1 order by a;
MySQL uses an index for ordering.
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'o1', 'index', NULL, 'oindex', '103', NULL, '8', 'Using index'
Now re-create the partial index:
drop index oindex on o1;
create index oindex on o1 (a (2) );
explain select a from o1 order by a;
MySQL is now trying "filesort".
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'o1', 'ALL', NULL, NULL, NULL, NULL, '8', 'Using filesort'
For searches, a partial index is useful because MySQL may simply discard these values from not being fully matched. For ORDER BY, MySQL may not have that kind of luck. In the above case, even I create a "partial index" for the max. column length, MySQL still doesn't use an index for ORDER BY.
source to share
The performance issue is the offset value expressed in the sentence limit
. If you are reading a table you can store the values and use >
before order by
:
select title
from movies
where title > $title
order by title
limit 50000;
If the $title
heading is on line 150,000, then it should go fast. Based on the results of this query, you would have to reset $title
for the next query.
I'm surprised that relatively few lines take tens of seconds. It should go faster when the index is in memory. Another complicating factor is that it title
can be long - so the index can take many tens or hundreds of megabytes. This is still not that important, but it will introduce a noticeable delay.
source to share