In-depth analysis of limit usage in Mysql _ MySQL-mysql tutorial

I haven't used mysql limit for a long time. I thought it was an error (limit starts and ends). the correct one is (limit offset, number of items ), to remember this error, repeat a detailed description of limit usage. I recommend it to you and hope it will help you. Mysql limit usage: when we use a query statement, we often need to return the first few or a few rows of data. what should we do at this time? Don't worry, mysql already provides us with such a function.

SELECT * FROM table LIMIT [offset,] rows | rows 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 specified, the first parameter specifies the offset of the first returned Record Row, and the second parameter specifies the maximum number of returned record rows. The OFFSET of the initial Record Row is 0 rather than 1. to be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # OFFSET #.
Mysql> SELECT * FROM table LIMIT 5, 10; // retrieves records FROM 6 to 15 rows.
// To retrieve all record rows from an offset to the end of the record set, you can specify the second parameter-1:
Mysql> SELECT * FROM table LIMIT 95,-1; // retrieves 96-last Records.
// If only one parameter is specified, it indicates the maximum number of record rows returned:
Mysql> SELECT * FROM table LIMIT 5; // retrieve the first five record rows
// In other words, LIMIT n is equivalent to LIMIT 0, n.
Note the differences between limit 10 and limit 9 and 1:
For example:
1.

The code is as follows:

Select * From cyclopedia Where ID> = (
Select Max (ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) Limit 100;

2.

The code is as follows:

Select * From cyclopedia Where ID> = (
Select Max (ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) Limit 100;

Is it faster to get 90000 records after 100, 1st sentences or 2nd sentences?
The first 1st records are obtained first, and the largest ID value is used as the start ID. then, the first 90001 records can be quickly located.
The 2nd clause selects only the first record after the first record, and then takes the ID value as the starting marker to locate the next 90000 records.
1st statement execution result. 100 rows in set (0.23) sec
2nd statement execution result. 100 rows in set (0.19) sec
In fact, the 2nd sentence can be simplified:

The code is as follows:

Select * From cyclopedia Where ID> = (
Select ID From cyclopedia limit 90000,1
) Limit 100;

Using the IDs of 90,000th records directly does not require the Max operation. in this way, the theoretical efficiency is higher, but the results are almost invisible in actual use, because the ID returned by the positioning itself is a record, Max can get the result almost without operation, but this write is clearer, saving the time to draw a snake.

The code is as follows:

Select Top 100 * From cyclopedia Where ID> = (
Select Top 90001 Max (ID) From (
Select ID From cyclopedia Order By ID
) As tmp
)

But whether the implementation method is stored in the process or direct code, the bottleneck is always that the TOP of the MS-SQL is always to return the first N records, this situation
However, if the data volume is small, the efficiency will be low if there are hundreds of thousands. In contrast, MySQL limit has many advantages.
, Run:

The code is as follows:

Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1

The results are as follows:

90000 rows in set (0.36) sec
1 row in set (0.06) sec

The MS-SQL can only use Select Top 90000 ID From cyclopedia for execution time is 390 ms, the same operation time is less than MySQL 360 ms.
The offset of limit is used when there are too many records, and the offset is small. it is better to use limit directly. The greater the offset, the better the latter.

1. when the offset value is small.

The code is as follows:

Select * from yanxue8_visit limit 10, 10

Run multiple times and keep the time between 0.0004 and 0.0005

The code is as follows:

Select * From yanxue8_visit Where vid> = (
Select vid From yanxue8_visit Order By vid limit 10, 1
) Limit 10

Run multiple times, and the time is kept between 0.0005-0.0006, mainly 0.0006
Conclusion: When the offset is small, it is better to use limit directly. This display is the cause of the subquery.

2. when the offset value is large.

Select * from yanxue8_visit limit, 10
Run multiple times and keep the time at around 0.0187

The code is as follows:

Select * From yanxue8_visit Where vid> = (
Select vid From yanxue8_visit Order By vid limit, 1
) Limit 10

Run multiple times, with a time of around 0.0061, only 1/3 of the former. The larger the offset value, the better the latter.
Mysql> SELECT * FROM table LIMIT 95,-1; // retrieves 96-last Records.
// If only one parameter is specified, it indicates the maximum number of record rows returned.

The above is all the content of this article. I hope you will like it.

Please take a moment to share your article with your friends or leave a comment. Thank you for your support!