Mysql limit Basic usage

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

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
The LIMIT clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. The parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first returned record line, and the second parameter specifies the maximum number of returned record lines. The offset of the initial record line is 0 (not 1): In order to be compatible with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #. 
mysql> SELECT * FROM table LIMIT 5,10; // Retrieve record rows 6-15
  
//In order to retrieve all the record rows from a certain offset to the end of the record set, you can specify the second parameter as -1:
mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record row 96-last.
  
//If only one parameter is given, it means to return the maximum number of record rows:
mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 rows
  
//In other words, LIMIT n is equivalent to LIMIT 0,n. 
mysql> SELECT * FROM table LIMIT 5,10; // Retrieve record rows 6-15
//In order to retrieve all the record rows from a certain offset to the end of the record set, you can specify the second parameter as -1:
mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record row 96-last.
//If only one parameter is given, it means to return the maximum number of record rows:
mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 rows
//In other words, LIMIT n is equivalent to LIMIT 0,n. 
MySQL Limit clause
The 　　Limit clause can be used to force the SELECT statement to return the specified number of records. Limit accepts one or two digital parameters. The parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first returned record line, and the second parameter specifies the maximum number of returned record lines.
　　//The offset of the initial record line is 0 (instead of 1):
　　Mysql> SELECT * FROM table LIMIT 5,10; //Retrieve record rows 6-15
　　//In order to retrieve all the record rows from a certain offset to the end of the record set, you can specify the second parameter as -1:
　　Mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record row 96-last
　　//If only one parameter is given, it means to return the maximum number of record rows. In other words, LIMIT n is equivalent to LIMIT 0,n:
　　Mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 rows 
How efficient is Limit?
　　 The high execution efficiency of Limit, which is often said, is for a specific condition: that is, the number of databases is large, but only a part of the data needs to be queried.
　　 The principle of high efficiency is to avoid full table scans and improve query efficiency.
　　 For example: each user’s email is unique. If the user uses email as the user name to log in, he needs to query a record corresponding to the email.
　　SELECT * FROM t_user WHERE email=?;
　　 The above statement realizes the query of a piece of user information corresponding to email, but because the column of email is not indexed, it will lead to a full table scan, and the efficiency will be very low.
　　SELECT * FROM t_user WHERE email=? LIMIT 1;
　　 plus LIMIT 1, as long as the corresponding record is found, the downward scan will not continue, and the efficiency will be greatly improved.
 
Limit's low efficiency?
　　 In one case, the use of limit is inefficient, that is: only use limit to query the statement, and the offset is particularly large
　　 do the following experiments:
      Statement 1:
         　　Select * from table limit 150000,1000;
　　 Statement 2:
         　　Select * from table while id>=150000 limit 1000;
　　 sentence 1 is 0.2077 seconds; sentence 2 is 0.0063 seconds
　　 The time ratio of the two sentences is: sentence 1 / sentence 2 = 32.968
 
When comparing the above data, we can find that the performance of using where...limit... is basically stable, and is not greatly affected by the offset and the number of rows, while the limit is greatly affected by the offset. When the offset reaches a certain value, the performance begins to drop significantly. However, when the amount of data is small, there is little difference between the two.
　　 So you should use where and other query statements first, and use it with limit to achieve high efficiency
limit execution efficiency 
mysql paging directly uses limit start, size paging statements:
select * from product limit start, size
When the starting page is small, the query has no performance problems. Let's look at the execution time of paging from 10, 100, 1000, 10000 (20 entries 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. This shows that the paging statement limit has a lot to do with the starting page number. Then we change the starting record to 40w and look at it (that is, the record About half of)
select * from product limit 400000, 20 3.229 seconds
Look at the time we took the last page of records
select * from product limit 866613, 20 37.44 seconds
Obviously this kind of time is unbearable for the largest page number page of this kind of paging.
From this we can also summarize two things:
The query time of the limit statement is proportional to the position of the starting record.
The limit statement of mysql is very convenient, but it is not suitable for direct use for tables with a lot of records. 
Optimization plan 1 
In our business, self-increasing ID is used, and there is no special sorting requirement for paging query, and it can be sorted according to ID. In response to this situation, using the order of ID, the paging logic is modified as follows.
select * from product where id> #{id} limit 0, size
When querying, take the largest ID of the last query as a parameter. Through testing, this kind of query efficiency is very high, and the query efficiency has nothing to do with the page number, querying any page, almost the same query efficiency is obtained. However, this optimization has limitations and can only be used when there is no requirement for sorting. And it also destroys the unified paging logic.
Is there a unified plan? Let’s look at optimization plan two
Optimization plan 2
We all know that if only the index column (covering index) is included in the statement that uses the index query, the query will be very fast in this case.
Because there is an optimized algorithm for index search, and the data is on the query index, there is no need to find the relevant data address, which saves a lot of time.
In addition, there are related index caches in Mysql, and it is better to use the cache when the concurrency is high.
In our example, we know that the id field is the primary key, so it naturally contains the default primary key index. Now let us look at the effect of the query using the covering index:
This time we query the data of the last page (using the 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 of querying all columns.
So if we want to query all the columns, there are two ways,
id>= form:
SELECT * FROM product WHERE ID> = (select id from product limit 866613, 1) limit 20
Use join
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
The execution efficiency of these two kinds of queries is similar, but compared with no optimization money, it has been greatly improved.
The second optimization scheme is more versatile, but the efficiency is not as high as the first one. Choose according to your own business.