Detailed description of mysql limit paging Optimization

Paging in mysql will provide us with the limit command for direct paging. If several pieces of data are directly paginated using the limit command, there is no problem, but if tens of thousands or millions of data, you will find that the machine will be stuck every day.

To implement paging, we generally use select * from table where id> 100 limit a, B. When the page size is too large, for example, if the page size reaches 10 thousand, a = 100,000, B = 10, although the index is used here (it will be worse if no index is needed), after index retrieval, you still need to retrieve 100,010 data records from the table and then discard the first 100,000 data records, and return 10 results, the cost is too high.
 

The general solution is to limit the total number of pages, because no user will be interested in the data after the first page, of course, it can also be achieved through coreseek sphinx and so on.
 

Of course, it can also be optimized through SQL. Next we will implement it through a subquery.

The Code is as follows:	Copy code
Select * from table join (select id from table where id> 100 limit, 10) as B on table. id = B. id

 

In clause select id from table where id> 100 limit, 10, because the column id has an index, the query will be directly searched and returned through the index. You do not need to go back to the table to find rows.

Then, the outer join operation will directly check the database based on the associated id to obtain data.

Instead of using limit directly, we first get the offset id and then use limit size to get data. Based on his data, it is much better to use limit directly. Here I use data in two cases for testing. (Test environment: win2033 + p4 dual-core (3 GHZ) + 4G memory MySQL 5.0.19)

1. When the offset value is small.

Select * from yanxue8_visit limit 10, 10

Run multiple times and keep the time between 0.0004 and 0.0005

The Code is as follows:	Copy code
Select * From yanxue8_visit Where vid> = ( Select vid From yanxue8_visit Order By vid limit 10, 1 ) Limit 10

Run multiple times, and the time is kept between 0.0005-0.0006, mainly 0.0006

Conclusion: When the offset is small, it is better to use limit directly. This is obviously the cause of subquery.

2. When the offset value is large.

Select * from yanxue8_visit limit, 10

Run multiple times and keep the time at around 0.0187

The Code is as follows:	Copy code
Select * From yanxue8_visit Where vid> = ( Select vid From yanxue8_visit Order By vid limit, 1 ) Limit 10

Run multiple times, with a time of around 0.0061, only 1/3 of the former. It can be predicted that the larger the offset, the higher the latter.
Pay attention to correct your limit statement and optimize MySQL later.