drquincy — 2013-07-23T13:44:13-04:00 — #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?
r937 — 2013-07-23T18:48:55-04:00 — #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)
drquincy — 2013-07-25T12:28:19-04:00 — #3
LOL, I know. I guess I was just testing the theory. Thanks for the reply.