MySQL paging limit speed too slow optimization method

When a table data has millions of of data, pagination is a problem
such as SELECT * 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

A recent web site's server reviews were brushed to death, causing MySQL database anomalies to occur too many open connections

The SQL statement raised

The code is as follows	Copy Code
SELECT A.uid, A.veil, A.content, A.datetimes, A.audit, B.user_name, b.uc_id From News_talkabout A Left JOIN users_info b on a.uid = b.ID WHERE infoid =11087 ORDER BY a.ID DESC LIMIT 451350, 30

Throw it in the phpMyAdmin, it's slow.
What are the factors that make people think?

The code is as follows	Copy Code
SELECT A.uid, A.veil, A.content, A.datetimes, A.audit, B.user_name, b.uc_id From News_talkabout A Left JOIN users_info b on a.uid = b.ID WHERE infoid =11087 ORDER BY a.ID DESC LIMIT 0, 30

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 database 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 from Wl_tagindex
Where byname= ' F ' ORDER by ID limit 300000,10
) A
Left join Wl_tagindex 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

The code is as follows

Copy Code

Mysql> set profiling=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

The code is as follows	Copy Code
SELECT * from ' abc ' WHERE ' BatchId ' = 123 LIMIT 839960, 40

Time: 1.8696 seconds

Reverse Lookup sql:
SQL code

The code is as follows	Copy 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

The code is as follows	Copy 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

The code is as follows	Copy 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