Using index on ORDER BY
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?
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)
LOL, I know. I guess I was just testing the theory. Thanks for the reply.
Originally Posted by r937