Use the Limit parameter to optimize MySQL queries. I read a program written by a foreigner a few days ago and used a lot of Limit keywords in MySQL queries. this made me very interested, because I was impressed, the Limit keyword seems to be more used to read a program written by a foreigner a few days ago, and many Limit keywords are used in MySQL queries, which makes me very interested, because in my impression, the Limit keyword seems to be used by more MySQL database programmers for querying pages (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 statement:
SELECT * FROM
(SELECT a1. *, rownum _
FROM testtable a1
WHERE rownum> 20)
WHERE rownum_< = 1000
This statement can query 20 to 1000 records in the testtable table, and also requires nested queries, the efficiency is not too high, 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 are returned.
The implementation syntax is indeed simple, but it is difficult to compare the efficiency of the two SQL statements here, because the Limit option in MySQL has many different interpretations, the speed varies greatly in different methods. Therefore, we cannot simply say who is more efficient.
But for programmers, it is easy enough because the maintenance cost is low.
The following describes the Limit syntax:
SELECT ....... -- Other parameters of the Select statement
[LIMIT {[offset,] row_count | row_count OFFSET}]
Here, offset is the offset (the starting address of this offset is 0, not 1, which is easy to make a mistake). as the name suggests, it is the position to leave the starting point, and row-count is also very simple, is the limit on the number of returned Records.
Eg. SELECT * FROM testtable a limit 10, 20 where ....
In this way, 20 records that meet the where condition can be returned after 10 rows (including 10 rows.
If no constraints exist, 10 to 29 rows of records are returned.
So what does this have to do with avoiding full table scanning? The following describes how to optimize the Limit parameter scan in the MySQL manual:
In some cases, when you use the LIMIT option instead of HAVING, MySQL will process the query in different ways.
L If you use LIMIT to select only some of the rows, MySQL typically performs a full table scan, but in some cases it uses indexes (related to ipart ).
L If you use LIMIT n and order by at the same time, after MySQL finds the first qualified record, it will end the sorting instead of sorting the entire table.
L when LIMIT n and DISTINCT are used at the same time, MySQL will stop querying after a record is found.
L in some cases, group by can be solved BY reading the key sequentially (or sorting the key), and then calculating the summary until the key value changes. In this case, the LIMIT n does not calculate any unnecessary GROUP.
L when MySQL completes sending the nth row to the client, it will discard the remaining query.
L LIMIT 0 always returns an empty record quickly. This is useful for checking the query and obtaining the column type of the result column.
L The size of the temporary table uses LIMIT # to calculate the amount of space required for query.
The Limit MySQL Query uses a lot of Limit keywords, which makes me very interested, because in my impression, the Limit keyword seems to be made more...
