SQL Limit Optimization method

Typically, the Limit keyword can accept one or two numeric parameters. It is important to note that this parameter must be an integer constant. If the user gives two parameters, the first parameter represents the offset of the first row that returned the record, and the second parameter represents the maximum data that returns the row of the record. Another caveat is that the initial record line is offset by 0 instead of 1. A lot of users will make mistakes here.

Although you use the limit statement to limit the number of records returned, you can increase your application's productivity. But it can also bring some negative effects on the performance of the system. If it could result in a full table scan, and so on. To this end, the author gives some suggestions for the optimization of limit keywords for reference.

Recommendation I: Flexible use of limit 0 clause

Depending on the definition of the Limit keyword, if the argument is 0, it returns an empty record. It doesn't seem to have much meaning. Fact In practical work, flexible use of this 0 parameters, can bring us a lot of harvest.

As now the Database tutorial engineer wants to confirm the validity of a query, and if you run the query directly, you need to wait for the record that it returns. If the number of records involved is higher, or the computational logic is more complex, it will take a long time to wait. You can use the Limit 0 clause in the SELECT query statement at this point. This allows the database to quickly return an empty collection as long as the query statement has no grammatical errors. Thus help the database Designer to quickly judge the validity of the query statement. In addition, this empty set and also returns the data type of each field in a table. This limit 0 clause also allows you to query the table structure of a table.

It can be seen that the flexible application of the Limir 0 clause, does bring us a very small profit. It should be noted, however, that this clause may not work in certain situations. As a rule, this limit 0 clause is not supported in the monitor's working environment. The result will only show empty set, not the result we need.

Recommendation two: limit is used in conjunction with GROUP by

The group BY keyword is used primarily to subtotal data. However, before subtotals, it is often necessary to sort the advanced data. When the limit statement is used to specify the number of results to display, it is often necessary to refer to the problem of subtotal and sorting records. As now in a school performance management system, the student's total score needs to be sorted. That is to summarize the students ' scores and then show their top 50 records. You need to use both the GROUP BY clause and the LIMIT clause at this point. In fact, we can see from this case that these two clauses depend on each other's characteristics. Because of this feature, which is often used in conjunction with each other, combining the GROUP BY clause can improve the query efficiency of limit.

This is mainly because both if used together, the Limit keyword will not recalculate any unnecessary group by values. In other words, in some cases, the GROUP BY clause can be used in order to read the key or sort on the key to solve the sorting problem at the subtotal, and then calculate the summary until the value of the keyword changes. In this case, two clauses need to do some common work, just do it once. This can be used from another angle to improve the performance of the application system. It is more efficient to use a query statement to extract a specific number of records than to make a single view of the data subtotals. Because the latter is to use two clauses separately, you cannot enjoy the advantages embodied in the combined use.

Recommendation three: Use Sql_calc_found_rows to increase the flexibility of clauses

By default, the LIMIT clause returns the number of record rows specified by the user. The database system discards the remaining queries as long as the database has sent the number of rows required by the user. In this case of student achievement, the database returns only 50 records and then terminates the query job if the user only needs to return to the top 50 of the total score.

However, in certain circumstances, the user may still need to continue the subsequent query? If the user out of the query some specific records, but also need to know the total number of records, at this point how to deal with? If users now need to know the top 50 of the student information, but also need to know the total score of more than 500 points in the overall number. Using the limit clause alone may not meet the needs of the user because it only cares about the previous 50 records. If you want to implement this requirement, often need to combine sql_calc_found_rows keyword.

The primary purpose of this keyword is to be able to prepare the database administrator for the number of records in the query that conforms to the WHERE condition statement. The user can then obtain the total number of records that meet the criteria only after a SELECT Found_rows statement is subsequently executed. However, it is important to note that the use of this keyword will have some side effects. That is, the query statement with this keyword cannot use the data cache. Therefore, in some cases, the performance of data query can be reduced. In general, this keyword is used only in situations where the case statement is more complex. Of course, this is a performance-driven proposition, not a technical one. That is, you can use this keyword even if the WHERE condition statement is not complex, and there is no grammatical error. But it is not ideal in performance.

Recommendation IV: Special phenomena when used in conjunction with the DISTINCT keyword

The DISTINCT keyword is used primarily to filter duplicate records. The Limit keyword is used primarily to specify the number of rows returned by the record. What happens if these two keywords are used together? The database returns the specified number of distinct records, if understood literally. If the limit parameter is 50, the database returns 50 records that are not duplicates. The subsequent query will then stop. If there are duplicate records in the query's records, the actual number of database queries is often more than the number specified by the Limit keyword.

In practical work, the function of this statement is still very big. If you have a table of employee attendance information now. The database administrator now needs to count the number of absences in the top 20. To prevent duplicate records at this point, you can add a distinct keyword to the query to filter the number of duplicate records. This eliminates the need to use multiple query statements to accomplish this requirement.

Recommendation five: The relationship between limit and the index

If the database administrator decides to use the limit clause to specify the number of records to display, it is best to use the index to the maximum to avoid full table scans and improve productivity. That is, when the database chooses to do a full table scan, you can use the index in some cases.

As now the database administrator decides to use the limit clause with the ORDER BY clause. Once the database finds the first rowcount row of the sort result, the system will end the sort, not the entire table. The entire table is sorted if the ORDER BY clause is used separately. Even so, sorting must be a waste of time. If the database administrator decides to use the index at this point, it can greatly improve the efficiency of the query.

Look at a foreigner who wrote

Sometimes we may not be want to retrieve all records that satsify the critera specified in where or having clauses.

In the MySQL tutorial, this is accomplished using the limit keyword. The syntax for limit is as follows:

[SQL statement 1]
Limit [n]

where [n] is the number of records to be returned. Please, that's the ORDER BY clause are usually included in the SQL statement. Without the "ORDER BY clause", the results we get would being dependent on what the "database default is."

For example, we'll wish to show the two highest sales amounts in table store_information

Table store_information

Store_name	Sales	Date
LOS Angeles	$1500	jan-05-1999
San Diego	$	jan-07-1999
San Francisco	$300	jan-08-1999
Boston	$700	jan-08-1999

We key in,

Select Store_name, Sales, date
From Store_information
ORDER BY sales Desc
Limit 2;

Result :

Store_name	Sales	Date
LOS Angeles	$1500	jan-05-1999
Boston	$700	jan-08-1999