Using index on ORDER BY

Databases
1

The documentation on the MySQL seems lacking in this area.

If you had a table where you typically select everything a “page” at a time using LIMIT x, y and ORDERing BY a column, is it worth adding an index to the ORDER BY column?

Let’s say you had a simple table with 100k records:

id, name, html

And you’re doing queries like:

SELECT * FROM table ORDER BY name LIMIT 50000, 50;

Can you optimise this or will it always use filesort?

Thanks.

2

yeah, that will be optimized by an index on name

note: i have yet to see a real world application where there is a user patient enough to hit the “next” link one thousand times (to get to the scenario you used, LIMIT 50000,50)

3

LOL, I know. I guess I was just testing the theory. Thanks for the reply.