How to get rid of order by or optimize the query

I have got query

select field from table order by field asc limit 4000, 30;

Table has got million rows order by on such simple select make big delays. There is index on field. How to get rid of 'order by' and speed up the limit and obtain the same result via select?

My proposition is to change the order of rows and then just make simple select field from table. Could you suggest better idea?开发者_开发问答

The data type of a field has an effect on how fast order by will be. Integer columns are generally fastest for this, and text columns the slowest. If the datatype of the column you're sorting by is textual I'd suggest using a numeric column for ordering instead.

Indexing the column you're ordering by should help performance, but MySQL might ignore it and do a full table scan. FORCE INDEX might be of benefit in speeding up the sorting.

Changing the physical order of the columns in the table will not have a predictable effect, as default ordering for rows is not defined in MySQL, or indeed any SQL database that I know of. As rows get added, altered and deleted, the physical position of the row in the table will change over time so you can't depend on it for ordering.

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ has some useful tips on optimizing sorts.