MySQL Limit paging optimization

A recent project encountered MySQL limit page odd slow, the number of views is about 1 million, but the query of a page of data needs more than two minutes, the SQL statement is about the order by limit, the online optimization is probably in the index, the optimization sub-query This kind, have tried, Speed does not have a big lift.

Later, the brain hole open, is not the order by the question, think of 1 million data sorting should also be very time-consuming, so the order by delete, indeed, 2.5 actually shortened to 0.045 seconds. After testing if the query is a single table, the effect of order by on speed is less obvious, and when querying and correlating and subqueries, the effect on speed is obvious.

MySQL Limit paging optimization