Why is my MySQL SELECT statement with ORDER BY so slow even though an INDEX is on the column?

Question

I have a movies table. It has 1.3 million rows in it.

The table has an INDEX on the title column, order asc, length 255.

The title column itself is a VARCHAR(1000).

Even with that setup, the following query takes 8 second to run. Anyone ideas or shots in the dark as to why this may be? I'm stumped because it seems like such a basic problem to solve.

SELECT title
FROM movies 
ORDER BY title
LIMIT 150000, 50000

When I take out the ORDER BY, the query is super fast (0.05 seconds):

SELECT title
FROM movies 
LIMIT 150000, 50000

Answer 1

The problem with performance is the offset value expressed in the limit clause. If you are reading through the table, then you can store the values and use a > before the order by:

select title
from movies
where title > $title
order by title
limit 50000;

If $title is the title at row 150,000 then this should go fast. Based on the results from this query, you would reset $title for the next query.

I am surprised that relatively few rows takes tens of seconds. It should go faster once the index is in memory. One additional complicating factor is that the title might be long -- so the index might be occupying many tens or hundreds of megabytes. That is still not that big, but it would introduce a noticeable delay.

Answer 2

Edit: prefix index is a better name than partial index I used.

Since your index is a partial index, MySQL may not use it for the order by and still have to sort the values by their full lengths.

Let's 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 is using index for the order by.

     # 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 a 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 searching, partial index is helpful since MySQL can just drop those values not completely matched. For ORDER BY, MySQL may have no such luck. In above case, even I create a "partial index" for the max. length of column, MySQL is still not using the index for ORDER BY.

Answer 3

To search on something like a title, you would be much better off using FULLTEXT and MATCH(...) AGAINST(...).