A brief discussion on the implementation of MySQL's limit paging optimization solution

Mysql limit paging statement usage

Compared with Oracle and MS SqlServer, MySQL's paging method is so simple that it makes people want to cry.

--grammar:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

--Example:

select * from table limit 5; --Return the first 5 rows select * from table limit 0,5; --Same as above, return the first 5 rows select * from table limit 5,10; --Return rows 6-15

How to optimize limit

When a query statement has a large offset, such as select * from table limit 10000,10 , it is best not to use limit directly. Instead, get the offset id first, and then use limit size directly to get the data. The effect will be much better.

like:

select * From customers Where customer_id >=(
select customer_id From customers Order By customer_id limit 10000,1
) limit 10;

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 took 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.

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.

Using join

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

The query time is also very short, great!

In fact, both use the same principle, so the effects are similar.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.