A few days ago saw a foreigner wrote the program, in the MySQL query using a lot of Limit keywords, which makes me very interested, because in my impression, the Limit keyword seems to be more used by the MySQL database programmer to do query paging (of course, this is also a good query optimization), So here's an example, let's say we need a paged query, which is typically implemented in Oracle with the following SQL sentences:
SELECT * FROM
(SELECT a1.*, rownum rownum_
from TestTable A1
WHERE rownum > 20)
WHERE rownum_ <= 1000
This statement can query to the TestTable table of 20 to 1000 records, but also need to nest query, the efficiency is not too high, see MySQL implementation:
SELECT * from testtable A1 limit 20,980;
This returns a record of 21 to (20 + 980 =) 1000 in the TestTable table.
The implementation of the syntax is really simple, but if you want to say the efficiency of the two SQL statements here, it is difficult to compare, because in MySQL, the LIMIT option has a number of different interpretations, the speed difference in different ways is very large, so we can not from the simplicity of the statement to say who efficiency high.
But for programmers, simple enough, because the maintenance cost is low, hehe.
Let's talk about this Limit syntax:
Additional parameters for SELECT ....--select statements
[LIMIT {[offset,] row_count | row_count offset Offset}]
Here offset is the offsets (the starting address of this offset is 0, not 1, which is easily mistaken) as the name suggests is the location to leave the starting point, and Row-count is very simple, is the number of records returned limit.
Eg. SELECT * from TestTable a limit 10,20 where ....
This enables the result to return 20 records that meet the Where condition after 10 rows (including 10 rows of themselves).
Then, if there are no constraints, a record of 10 to 29 rows is returned.
What does this have to do with avoiding full-scale scanning? The following is a description of the MySQL manual for the Limit parameter optimization scan:
In some cases, when you use the LIMIT option instead of having a having, MySQL will process the query in different ways.
L If you use LIMIT to select only a subset of the rows, when MySQL generally does a full table scan, but in some cases the index is used (related to IPART).
L If you use LIMIT n with ORDER by, after MySQL finds the first qualifying record, it ends the sort instead of sorting the entire table.
When LIMIT N and DISTINCT are used simultaneously, MySQL will stop querying after a record is found.
In some cases, GROUP by can be solved by sequentially reading the key (or sorting on the key) and then calculating the digest until the key value is changed. In this case, LIMIT n will not calculate any unnecessary GROUP.
When MySQL finishes sending the nth line to the client, it discards the remaining queries.
L The LIMIT 0 option always returns an empty record quickly. This is useful for checking the query and getting the column type of the result column.
The size of the temporary table uses the LIMIT # to calculate how much space is required to resolve the query.
The above describes the selllimit using the limit parameter to optimize the MySQL query method, including the content of the selllimit, I hope that the PHP tutorial interested in a friend helpful.
