Methods of optimizing MySQL query using limit parameters _php tips

A few days ago, I saw a program written by a foreigner, I'm interested in using a lot of Limit keywords in mysql queries, because in my memory, the Limit keyword seems to be more used by programmers using MySQL databases to do query paging (which is also a good query optimization), So here's an example, assuming we need a paging query, Oracle typically implements the following SQL sentences:
Select * FROM
(select a1.*, rownum rownum_
from TestTable A1
where rownum >)
where rownum_ <= 1000
This statement can query 20 to 1000 records in the TestTable table, and requires nested queries that are not too efficient to see MyS QL implementation:
SELECT * from testtable A1 limit 20,980;
This returns a record of 21 to (20 + 980 =) 1000 in the TestTable table. The
implementation syntax is really simple, but if you want to say the efficiency of the two SQL statements here, it's hard to compare, because there are many different ways to interpret the Limit option in MySQL, and the speed difference in different modes is great, so we can't say whose efficiency is high from the simplicity of this statement.
But for programmers, simple enough, because the maintenance cost is low, hehe.
Here's a Limit syntax:
SELECT ... the other parameters of the--select statement
[Limit {[offset,] row_count | row_count offset offset}]
here Offset is offsets (the starting address for 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 also very simple, is the number of records to return the limit.
Eg. SELECT * from TestTable a limit 10,20 where ....
This allows the result to return 20 records that match the Where condition after 10 rows (including 10 rows themselves).
then returns a record of 10 to 29 rows if there is no constraint.
What does that have to do with avoiding full table scans? Here is the MySQL manual forLimit parameter optimization Scan some instructions:
In some cases, when you use the LIMIT option instead of having a, MySQL will process the query in a different way.
L If you use LIMIT to select only a subset of the rows, when MySQL usually does a full table scan, but in some cases the index (related to IPART) is used.
L If you use LIMIT n with order BY, when MySQL finds the first qualifying record, it ends the sort instead of sorting the entire table.
L when LIMIT N and DISTINCT are in use at the same time, MySQL stops the query after it finds a record.
L In some cases, GROUP by can be resolved by sequentially reading the key (or sorting on the key), and then calculating the digest until the key value changes. In this case, LIMIT N will not compute any unnecessary GROUP.
L when MySQL completes sending the nth row to the client, it discards the remaining query.
L and 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
L Temp table uses LIMIT # to calculate how much space is needed to resolve the query.