A method to optimize the paging system

In the paging system, a bunch of limit offset is common, they are usually used with the order by, the index is useful for sorting, if there is no index will require a large number of file ordering, but in the Division also encountered a very common problem, such as limit 1000000,10 it's so cheap. Before you lose a lot of data, the cost of this operation is very high, and one of the techniques to improve efficiency is to cover the index on the cheap you can connect the data extracted from the overlay index with the full row of data, and then get the columns you need. Here's a column.

Select Film.id,film.description from film order by title limit 1000, 10;

This improves

Select Film.id,film.description from film inner join (select Film.id to Fulm order by title limit 1000,10) as XX using (ID);

This is a very efficient way, he lets the server check the index for potentially less data, and once you've got the rows you need to connect to the outside table, get the columns you need.