Mysql paging limit speed is too slow optimization method _mysql

In MySQL limit can achieve fast paging, but if the data to millions of when our limit must be optimized to effectively achieve a reasonable paging, otherwise may be stuck to your server oh.

When a table data has millions of of the data is a problem!

such as * from table limit 0,10 This is no problem when the limit 200000,10 data reading is very slow, you can follow the method to solve
The first page will be quick.
Percona Performance Conference 2009, several engineers from Yahoo brought a "efficientpagination Using MySQL" report
limit10000,20 the meaning of the scan to meet the conditions of the 10020 line, throw away the first 10000 lines, return to the last 20 lines, the problem is here.
LIMIT 451350, 30 scanned more than 450,000 lines, no wonder the slow is blocked.
But
Limit 30 Such statements scan only 30 rows.

So if we record the maximum ID before, we can make a fuss here.

As an example

Daily Paging SQL statements
Select Id,name,content from the users order by ID ASC limit 100000,20
Scan 100020 Lines
If the last maximum ID was recorded
Select Id,name,content from users where id>100073 order by ID ASC limit 20
Scan 20 lines.
The total data is about 5 million.
The following example selects * from Wl_tagindex where byname= ' F ' ORDER by ID limit 300000,10 execution time is 3.21s
After optimization:

 SELECT * FROM (
    select ID to wl_tagindex
    where byname= ' F ' ORDER by ID limit 300000,10
 ) a left
 join wl_t Agindex B on A.id=b.id

The execution time is 0.11s, the speed is obviously increased
Here need to explain is that I use the field is byname, id need to do these two fields to do composite index, otherwise, the effect of the increase is not obvious

Summarize

When a database table is too large, LIMIT offset, the offset value in length is too large, then the SQL query statement will be very slow, you need to increase the order by, and a form needs to be indexed.
If you use subqueries to optimize limit, the subquery must be contiguous and, in a sense, the subquery should not have a where condition, where it will filter the data and cause the data to lose continuity.
If you query a large number of records, and the amount of data transfer is large, such as containing the text Type field, you can create a subquery.

Select Id,title,content from the items WHERE ID in (select ID from the items order by ID limit 900000, 10);

If the offset of the limit statement is larger, you can reduce the offset = 0 by passing the PK key value, which is best int type and auto_increment

SELECT * from the users WHERE uid > 456891 ORDER by uid LIMIT 0, 10;

This statement, to the effect that the following:

SELECT * from the Users WHERE uid >= (select UID from the users order by UID limit 895682, 1) limit 0, 10;
If the value of limit offset is too large, users will page fatigue, you can set a maximum offset, more than can be processed, the general continuous paging is too large, the user experience is poor, you should provide a better user experience to the user.

Limit Paging Optimization method

1. Sub-Query optimization method
The first piece of data, then the ID that is greater than or equal to this data, is the data to get.
Disadvantage: The data must be continuous, it can be said that there is no where conditions, where conditions will filter data, resulting in data loss of continuity
Experiment under
Mysql> set Profi=1;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Select COUNT (*) from member;
+ ———-+
| COUNT (*) |
+ ———-+
| 169566 |
+ ———-+
1 row in Set (0.00 sec)
Mysql> Pager grep!~-
PAGER set to ' grep!~-'
Mysql> SELECT * from member limit 10, 100;
Rows in Set (0.00 sec)
Mysql> SELECT * from member where MemberID >= (select MemberID from member limit 10,1) limit 100;
Rows in Set (0.00 sec)
Mysql> SELECT * from member limit 1000, 100;
Rows in Set (0.01 sec)
Mysql> SELECT * from member where MemberID >= (select MemberID from member limit 1000,1) limit 100;
Rows in Set (0.00 sec)
Mysql> SELECT * from member limit 100000, 100;
Rows in Set (0.10 sec)
Mysql> SELECT * from member where MemberID >= (select MemberID from member limit 100000,1) limit 100;
Rows in Set (0.02 sec)
Mysql> Nopager
PAGER set to stdout
Mysql> Show PROFILESG
1. Row ***************************
Query_id:1
duration:0.00003300
Query:select Count (*) from member
2. Row ***************************
Query_id:2
duration:0.00167000
Query:select * from member limit 10, 100
3. Row ***************************
Query_id:3
duration:0.00112400
Query:select * from member where MemberID >= (select MemberID from member limit 10,1) limit 100
4. Row ***************************
Query_id:4
duration:0.00263200
Query:select * from member limit 1000, 100
5. Row ***************************
Query_id:5
duration:0.00134000
Query:select * from member where MemberID >= (select MemberID from member limit 1000,1) limit 100
6. Row ***************************
Query_id:6
duration:0.09956700
Query:select * from member limit 100000, 100
7. Row ***************************
Query_id:7
duration:0.02447700
Query:select * from member where MemberID >= (select MemberID from member limit 100000,1) limit 100
From the results, it can be found that the use of subquery method can effectively improve the performance when the deviation is more than 1000.
2. Inverted Table Optimization method
The inverted table method is analogous to indexing, maintaining pages with a single table, and then getting the data through an efficient connection
Disadvantage: Only suitable data number fixed situation, data can not be deleted, maintenance page table difficult
3. Reverse Lookup Optimization Method
When you offset more than half the number of records, first use the sort, so the offset is reversed.
Disadvantage: Order by optimization is more troublesome, to add indexes, indexes affect the efficiency of data modification, and to know the total number of records
, offset more than half of the data
Reference
Limit offset algorithm:
Forward lookup: (Current page –1) * page length
Reverse lookup: Total Record-current page * page length
Do the experiment and see how the performance
Total record number: 1,628,775
Number of records per page: 40
Total Pages: 1,628,775/40 = 40720
Middle page: 40720/2 = 20360
No. 21000 Page
Forward Lookup sql:
SQL code
SELECT * from ' abc ' WHERE ' BatchId ' = 123 LIMIT 839960, 40
Time: 1.8696 seconds
Reverse Lookup sql:
SQL code
SELECT * from ' abc ' WHERE ' BatchId ' = 123 ORDER by Inputdate DESC LIMIT 788775, 40
Time: 1.8336 seconds
No. 30000 Page
Forward Lookup sql:
SQL code
1.SELECT * from ' abc ' WHERE ' BatchId ' = 123 LIMIT 1199960, 40
SELECT * from ' abc ' WHERE ' BatchId ' = 123 LIMIT 1199960, 40
Time: 2.6493 seconds
Reverse Lookup sql:
SQL code
1.SELECT * from ' abc ' WHERE ' BatchId ' = 123 ORDER by Inputdate DESC LIMIT 428775, 40
SELECT * from ' abc ' WHERE ' BatchId ' = 123 ORDER by Inputdate DESC LIMIT 428775, 40
Time: 1.0035 seconds
Note that the result of the reverse lookup is descending desc, and the inputdate is the insertion time of the record, or the primary key union index, but not convenient.
4.limit Limit Optimization method
Limit the limit offset below a certain number. More than this number equals no data, I remember the Alibaba DBA said they did it.
5. Index only search method