Home > Article > Backend Development > How to use Limit parameters to optimize MySQL queries_PHP tutorial
How to use Limit parameters to optimize MySQL queries_PHP tutorial
- WBOYOriginal
- 2016-07-21 15:49:131030browse
A few days ago I saw 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 MySQL databases. Programmers use it to do query paging (of course this is also a good query optimization). Here is an example. 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_ <= 1000
This statement can query the testtable 20 to 1000 records in the table, and nested queries are required, which is not very efficient. Let’s take a look at MySQL’s implementation:
SELECT * FROM testtable a1 limit 20,980;
This will return 21 in the testtable table records to (20 + 980 =) 1000 records.
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 there are many different ways of interpreting the Limit option in MySQL, and the speed difference in different ways is huge. Large, 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 Shift (the starting address of this offset 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 10 to 29 rows 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.