Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use limit in MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Limit syntax

Limit syntax supports two parameters, offset and limit, the former indicating offset and the latter indicating fetching the previous limit data.

For example:

# # return the first 10 statements that meet the conditions select * from user limit 10 statements # return data items 11-20 that meet the conditions select * from user limit 10

It can also be seen from the above that limit n is equivalent to limit 0jinn.

Performance analysis.

In practice, we will find that loading slows down on some pages at the back of the page, that is to say:

Select * from user limit 1000000 million 10

Statement execution is slow. So let's test it first.

The first is to take 100 pieces of data when the offset is small. The total amount of data is about 200. Then gradually increase the offset.

Select * from user limit 0100-time consuming 0.03sselect * from user limit 10000100-time consuming 0.05sselect * from user limit 100000100-time consuming 0.13sselect * from user limit 500000100-time consuming 0.23sselect * from user limit 1000000100-time consuming 0.50sselect * from user limit 1800000100-time 0.98s

We can see that with the increase of offset, the performance is getting worse and worse.

Why is that? Because the syntax of limit 10000 mysql 10 is actually to find the first 10010 pieces of data and then discard the first 10000 rows, this step is actually wasted.

Optimize

Optimize with id

First find the maximum ID of the last page, and then query it using the index on id, similar to select * from user where id > 1000000 limit 100.

This is very efficient because there is an index on the primary key, but there is a disadvantage that the ID must be contiguous and the query cannot have where statements, because where statements will cause data to be filtered.

Optimize with overlay index

When mysql's query hits the index completely, it is called overwriting index, which is very fast, because the query only needs to look up on the index, and then it can be returned directly without going back to the data table to get the data. So we can first find out the ID of the index, and then get the data according to Id.

Select * from (select id from job limit 1000000100) a left join job b on a.id = b.id

It takes 0.2 seconds.

This is the end of this article on "how to use limit in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.