In-depth analysis of the usage of limit in Mysql _mysql

MySQL Limit usage: When we use the query statement, often to return the first few or the middle of a few lines of data, this time how to do? Don't worry, MySQL has provided us with such a feature.

SELECT * FROM table LIMIT [offset,] rows | Rows Offset Offset
The LIMIT clause can be used to force a SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first row to return the record, and the second parameter specifies the maximum number of rows to be returned. The offset of the initial record line is 0 (instead of 1): In order to be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # offset #.
Mysql> SELECT * FROM table LIMIT 5, 10; Retrieve Record Row 6-15
To retrieve all row rows from an offset to the end of a recordset, you can specify a second argument of-1:
Mysql> SELECT * from table LIMIT 95,-1; Retrieves the record row 96-last.
If only one argument is given, it represents the maximum number of record rows returned:
Mysql> SELECT * from table LIMIT 5; Retrieve the first 5 rows of records
In other words, LIMIT n is equivalent to LIMIT 0,n.
Note the differences between limit 10 and limit 9,1:
For example:
1.

Copy Code code 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.

Copy Code code 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;

Also take 90,000 after 100 records, the 1th sentence is fast or 2nd sentence fast?
The 1th sentence is to take the first 90,001 records, take one of the largest ID value as the starting ID, and then use it to quickly locate the next 100 records
The 2nd option is to take only 90,000 records after 1, and then take the ID value as the starting mark to locate the next 100 records
The 1th sentence executes the result. Rows in Set (0.23) sec
The 2nd sentence executes the result. Rows in Set (0.19) sec
In fact, the 2nd sentence can be simplified as follows:

Copy Code code as follows:

Select * FROM Cyclopedia Where id>= (
Select ID from Cyclopedia limit 90000,1
) limit 100;

Using the ID of the No. 90000 record directly, without the max operation, this is theoretically more efficient, but in practice it is almost invisible, because the location ID returns 1 records, and Max does not have to work to get the results, but this is clearer and clearer, eliminating the foot of the snake painting.

Copy Code code as follows:

Select Top * FROM Cyclopedia Where id>= (
Select Top 90001 Max (ID) from (
Select ID from Cyclopedia ORDER by ID
) as TMP
)

But whether the implementation is the storage process or direct code, the bottleneck is always ms-sql top always return the first n records, this Feeling
The condition is not deep when the data quantity is small, but if millions of million, the efficiency will certainly be low. In comparison, MySQL's limit has a lot of advantages.
Perform:

Copy Code code as follows:

Select ID from Cyclopedia limit 90000
Select ID from Cyclopedia limit 90000,1

The results were:

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

Ms-sql can only use the select top 90000 ID from Cyclopedia execution time is 390ms, performing the same operation time is also less than MySQL 360ms.
Limit offset (offset) is used to record more time, when the record is less, the offset is smaller, the direct use of limit better. The larger the offset, the better the latter.

1, offset relatively small time.

Copy Code code as follows:

SELECT * FROM Yanxue8_visit limit 10,10

Run multiple times, keeping the time between 0.0004-0.0005

Copy Code code as follows:

Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit order by vid limit 10,1
) Limit 10

Run multiple times, keeping the time between 0.0005-0.0006, mainly 0.0006
Conclusion: The direct use of limit is more excellent when offset is smaller. This display is the reason for the subquery.

2. When the offset is big.

SELECT * FROM Yanxue8_visit limit 10000,10
Run more than once and keep the time around 0.0187

Copy Code code as follows:

Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit order by vid limit 10000,1
) Limit 10

Run several times, the time remained at about 0.0061, only the former 1/3. The larger the offset, the better the latter.
Mysql> SELECT * from table LIMIT 95,-1; Retrieves the record row 96-last.
If only one argument is given, it represents the maximum number of rows returned

The above mentioned is the entire content of this article, I hope you can enjoy.

Please take a moment to share the article with your friends or leave a comment. We will sincerely thank you for your support!