MySQL paging and paging performance query optimization

MySQL paging uses limit directly to operate, limit if we directly without any processing may be large data will be very card.

one of the most common MySQL most basic paging methods:

Select * from content ORDER BY id desc limit 0,

In the case of small amounts of data, such SQL is sufficient, and the only problem to be aware of is to ensure that the index is used. As the amount of data increases, the number of pages is increasing, and looking at the next few pages of SQL can be similar:

SELECT * from content ORDER BY id DESC limit 10000, 10

Word, the more backward the page, the offset of the limit statement will be greater, the speed will be significantly slower.

MySQL Paging performance optimization

Limit 10000,20 The meaning of the scan satisfies the condition of 10020 lines, throw away the previous 10000 rows, return the last 20 rows, the problem is here, if it is limit 100000,100, need to scan 100100 lines, in a high concurrency application, Each query needs to scan more than 10W lines, the performance must be greatly compromised. It is also mentioned that limit n performance is not a problem, because only n rows are scanned.

The article mentions a "clue" approach, which provides "clues" to page flipping, such as SELECT * from message order by ID DESC, page by ID Descending, 20 per page, current 10th page, current page entry ID maximum is 9527, the smallest is 9500 , if we only provide jumps such as "previous page", "next Page" (No jump to page N), then in the case of "previous page" the SQL statement can be:

SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20;

No matter how many pages you turn, only 20 rows are scanned per query.

The disadvantage is that only the "previous page", "next page" link form, but our product manager very much like "< previous 1 2 3 4 5 6 7 8 9 Next >" How to Do?

If limit m,n inevitable, to optimize efficiency, only if possible to let M small, we extend the previous "clue" approach, or select * from the message order by ID DESC, according to the ID descending page, 20 per page, is currently the 10th page, The maximum current page entry ID is 9527, the smallest is 9500, such as to jump to page 8th, I read the SQL statement can be written like this:

SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20, 20;

Skip to page 13th:

SELECT * FROM message WHERE ID < 9500 ORDER by ID DESC LIMIT 40, 20;

And netizens say you can use sub-query to manipulate

One, sub-query paging way to improve paging efficiency, floating easy-to-use SQL statements are as follows:

The code below copies the code select * from ' content ' WHERE ID <=

(SELECT ID from the ' content ' ORDER by id desc LIMIT. ($page-1) * $pagesize. ", 1) ORDER by id desc LIMIT $pagesize

Why is that? Because subqueries are done on the index, and ordinary queries are done on a data file, the index file is generally much smaller than the data file, so it is more efficient to operate. (via) found by explain SQL statement: The subquery uses the index!

The code below duplicates the Code ID select_type table type possible_keys key Key_len ref rows Extra

1 PRIMARY Content Range PRIMARY PRIMARY 4 NULL 6264 Using where

2 subquery content index NULL PRIMARY 4 null 27085 Using Index

Through the floating easy measurement, the use of sub-query paging method of efficiency than the pure limit increased by 14-20 times!

Second, join paging mode

The code below copies the code select * from ' content ' as T1

JOIN (SELECT ID from ' content ' ORDER by id desc LIMIT.) ( $page-1) * $pagesize. ", 1) as T2

WHERE t1.id <= t2.id ORDER by t1.id desc LIMIT $pagesize;

After my testing, the efficiency of join paging and sub-query paging is basically on one level, and the time consumed is basically the same. Explain SQL statements:

The code below duplicates the Code ID select_type table type possible_keys key Key_len ref rows Extra

1 PRIMARY <derived2> system NULL NULL NULL NULL 1

1 PRIMARY T1 range PRIMARY PRIMARY 4 NULL 6264 Using where

2 DERIVED content index NULL PRIMARY 4 null 27085 Using Index

MySQL paging and paging performance query optimization