Why does MySQL paging become slower and slower when using limit?

Aniu just joined a new company. His first task was to export the data in the order table to a file based on conditions. Aniu thought: This is too easy, so he quickly wrote the following statement and told the tester that his code was an exempt product.

The statement is as follows:

select * from orders where name='lilei' and create_time>'2020-01-01 00:00:00' limit start,end

Unexpectedly, after being online for a period of time, production began to issue an early warning, showing that this SQL was a slow SQL with an execution time of more than 50 seconds, which seriously affected the business.
Aniu quickly asked the big brother Yuanyuan to help find the cause. Yuanyuan quickly solved it and did the following experiment for Aniu:

1. Test experiment

MySQL paging directly uses limit start, count paging statements:

select * from product limit start, count

When the starting page is small, the query has no performance issues. Let's look at the execution time of paging from 10, 100, 1000, and 10000 (20 records per page), as follows:

select * from product limit 10, 20 0.016 seconds select * from product limit 100, 20 0.016 seconds select * from product limit 1000, 20 0.047 seconds select * from product limit 10000, 20 0.094 seconds

We have seen that as the starting record increases, the time also increases, which shows that the paging statement limit is closely related to the starting page number.
Then we change the starting record to 40w (about half of the record)

select * from product limit 400000, 20 3.229 seconds

Let's look at the time when we get the last page of records

select * from product limit 866613, 20 37.44 seconds

For a page with the largest pagination number like this, this time is obviously unbearable.
We can also conclude two things from this:
The query time of the limit statement is proportional to the position of the starting record.
The MySQL limit statement is very convenient, but it is not suitable for direct use for tables with many records.

2. Performance optimization method for limit paging problem

2.1 Using the table's covering index to speed up paging queries

We all know that if the statement that uses an index query only contains that index column (covering index), the query will be very fast.
Because index search has an optimized algorithm and the data is on the query index, there is no need to look for the relevant data address, which saves a lot of time.
In addition, MySQL also has related index cache, and the effect will be better if the cache is used when the concurrency is high.
In our example, we know that the id field is the primary key, so the default primary key index is included. Now let's see how the query using the covering index performs:
This time we query the data of the last page (using a covering index, which only contains the id column), as follows:

select id from product limit 866613, 20

The query time is 0.2 seconds, which is about 100 times faster than the 37.44 seconds required to query all columns.
If we also want to query all columns, there are two ways.

2.2 Using the id>= format:

SELECT * FROM product 
WHERE ID > =(select id from product limit 866613, 1) limit 20

The query time is 0.2 seconds, which is a qualitative leap.

2.3 Using join

SELECT * FROM product a 
JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

Summarize:

Do you think I didn't give the reason? The reason is that when using select *, limit 600000 is used directly, 10 scans are about 600,000 data, and it is necessary to return to the table 600,000 times, which means that most of the performance is consumed in random access, and in the end only 10 data are used. If you find out the ID first and then query the records by association, it will be much faster, because the index can find the ID that meets the conditions quickly, and then return to the table 10 times. We can get the data we want.

This concludes the article on why MySQL paging becomes slower and slower with limit. For more information on MySQL paging limit slowness, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!