MySQL is an example of the optimization of the limit paging query through "delayed correlation"

recently encountered in the production of a paging query particularly slow problem, the amount of data is about 2 million of the appearance, turned to the last page performance is very low, almost 4 seconds to come out of the entire page, need to query optimization.
The first step is to find the SQL that executes slowly, as follows:
SELECT
shotel_id as Hotelid,
mroom_type_id as Mroomtypeid,
available_date as Availabledate,
Result_status as Resultstatus,
Create_time as Createtime,
Operate_time as Operatetime
From AUTOFS_IVR
ORDER by shotel_id
LIMIT 1983424,

explain a bit:
ID select_type table type possible_keys key Key_len ref rows Extra
1simpleautofs_ivrallnonenonenonenone1875402using File Sort

The observation is visible, type is all, the full table is scanned, extra is using the file sort, not the index overlay.
where the SELECT statement is selected except that the shotel_id remainder is not in the order by column, and the shotel_id column has an index on it, so this SQL does not go through the index overlay, each time according to a two-level index query to a record, We're going to have to go again. The primary key index goes to the table to find the other columns that are needed, naturally slow.

is there any way to optimize this limit paging query? To download the deferred correlation technique, you can optimize the SQL, and the following statements are optimized:
SELECT
shotel_id as Hotelid,
mroom_type_id as Mroomtypeid,
available_date as Availabledate,
Result_status as Resultstatus,
Operate_time as Operatetime,
Create_time as Createtime
From AUTOFS_IVR
INNER JOIN (
Select ID
From AUTOFS_IVR
ORDER by shotel_id
LIMIT 1983424,
) as Lim using (ID));

explain results are as follows:
ID select_type table type possible_keys key Key_len ref rows Extra
1primaryallnonenonenonenone20
1primaryautofs_ivreq_refprimaryprimary4lim.id1
2derivedautofs_ivrindexnoneix_sh_mr124none1875402using Index

Sub-query, using the index overlay technology, 20 records detected, and then through the primary key and the table itself to do the association, even if the full table scan, access records are only 20, query time reduced to 400 milliseconds, lifting speed 10 times times.

MySQL is an example of the optimization of the limit paging query through "delayed correlation"