selllimit How to optimize MySQL queries using the Limit parameter

A few days ago I read a program written by a foreigner, which used a lot of Limit keywords in MySQL queries. This made me very interested, because in my impression, the Limit keyword seems to be used more in programs that use MySQL databases. The operator is used to do query paging (of course this is also a good query optimization). Let’s take an example here. Suppose we need a paging query. Oracle generally uses the following SQL sentence to implement it:
SELECT * FROM
( SELECT a1.*, rownum rownum_
FROM testtable a1
WHERE rownum > 20)
WHERE rownum_ This statement can query 20 to 1000 records in the testtable table, and nested queries are also required. , the efficiency will not be too high, take a look at the implementation of MySQL:
SELECT * FROM testtable a1 limit 20,980;
In this way, 21 to (20 + 980 =) 1000 records in the testtable table can be returned.
The implementation syntax is indeed simple, but if you want to talk about the efficiency of the two SQL statements here, it is difficult to compare, because the Limit option in MySQL has many different interpretation methods, and the speed difference in different methods is huge. , so we cannot say who is more efficient based on the simplicity of this statement.
But for programmers, it’s good if it’s simple enough, because the maintenance cost is low, haha.
Let’s talk about the syntax of this Limit:
SELECT ……. --Other parameters of the Select statement
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
Here offset is the offset (this offset The starting address is 0, not 1, which is easy to mistake). As the name suggests, it is the position away from the starting point, and row-count is also very simple, which is the limit on the number of records returned.
Eg. SELECT * FROM testtable a limit 10,20 where ….
This will allow the result to return 20 records that meet the where condition after 10 rows (including the 10 rows themselves).
Then if there are no constraints, records from rows 10 to 29 will be returned.
So what does this have to do with avoiding full table scans? Here is some explanation from the MySQL manual about the Limit parameter optimization scan:
In some cases, MySQL will process the query differently when you use the LIMIT option instead of using HAVING.
l If you use LIMIT to select only a subset of rows, MySQL will generally do a complete table scan, but in some cases will use an index (related to ipart).
l If you use LIMIT n with ORDER BY at the same time, after MySQL finds the first record that meets the criteria, the sort will end instead of sorting the entire table.
l When LIMIT n and DISTINCT are used together, MySQL will stop the query after finding a record.
l In some cases, GROUP BY can be solved by reading the keys sequentially (or sorting on the keys), and then calculating the summary until the key value changes. In this case, LIMIT n will not evaluate any unnecessary GROUP s.
l When MySQL finishes sending the nth row to the client, it will abandon the rest of the query.
l And the LIMIT 0 option always quickly returns an empty record. This is useful for checking the query and getting the column types of the result columns.
l The size of the temporary table uses LIMIT # to calculate how much space is needed to solve the query.

The above introduces how selllimit uses the Limit parameter to optimize MySQL queries, including the content of selllimit. I hope it will be helpful to friends who are interested in PHP tutorials.