MySQL tips: optimize Limit with relevant parameters

When querying data, we usually need to specify the rows of data to be returned. For example, if there is a B/S architecture application, each page may display only 30 records. To improve the display efficiency, the database is generally required to return only thirty records at a time. When the user presses the next page, 30 records are returned from the database, and so on. This can shorten the data display time. This method is very effective when the base table to be queried is large. In this case, you can use the Limit keyword to implement this requirement. The Limit clause can be used to force the Select query statement to return the specified number of records.

Generally, the Limit keyword can accept one or two numeric parameters. Note that this parameter must be an integer constant. If you specify two parameters, the first parameter indicates the offset of the first record row to be returned, and the second parameter indicates the maximum data of the record row to be returned. Note that the offset of the initial record row is 0 rather than 1. Many users make mistakes here.

Although the Limit statement is used to Limit the number of returned records, it can improve the efficiency of the application. However, it also has some negative impact on the system performance. Such as full table scan. For this reason, the author provides some suggestions on Limit keyword Optimization for your reference.

Recommendation 1: flexible use of the Limit 0 clause

According to the definition of the Limit keyword, if the parameter is 0, it returns an empty record. This does not seem to make much sense. Actually not. In actual work, the flexible use of this 0 parameter can bring us great gains.

If the Database Engineer wants to confirm the validity of a query statement, if the query statement is run directly, it needs to wait for the record it returns. If the number of records involved is large, or the calculation logic is complex, it takes a long time. In this case, you can use the Limit 0 clause in the Select query statement. As long as the query statement has no syntax errors, the database can quickly return an empty set. This helps database designers quickly determine the validity of query statements. In addition, this empty set and medium return the Data Types of each field in a table. You can use this Limit 0 clause to query the table structure of a table.

It can be seen that the flexible application of the Limir 0 clause can indeed bring us a lot of benefits. However, this clause may not work in certain situations. Normally, the Limit 0 clause is not supported in the Monitor environment. In this case, only Empty Set is displayed, not the result we need.

Recommendation 2: Use Limit in combination with Group

The Group By keyword is mainly used to classify and summarize data. However, before classification and summarization, data advancement needs to be sorted. When the Limit statement is used to specify the number of displayed results, it usually involves the classification, summary, and sorting of records. For example, in a school Score Management System, the total score of students must be sorted. That is, the scores of each subject are summarized and the top 50 records are displayed. The Group By clause and the Limit clause must be used at the same time. In fact, we can also see from this case that the two clauses are mutually dependent. Because of this feature (often used in combination), the combination of Group By clauses can improve the query efficiency of Limit.

This is mainly because if the two are used together, the Limit keyword will not re-calculate any unnecessary Group By value. In other words, in some cases, the Group By clause can read keys in sequence or sort keys to solve the Sorting Problem During category aggregation, then the digest is calculated until the value of the keyword changes. In this case, you only need to do some common work for the two clauses once. This can be used to improve the performance of the application system from another perspective. It is more efficient than first performing a view to classify and summarize data and then using a query statement to extract a specific number of records. Because the latter uses two clauses separately, it cannot enjoy the advantages embodied in the combined use.

Recommendation 3: Use SQL _calc_found_rows to improve the flexibility of clauses.

By default, the Limit clause returns the number of records specified by the user. As long as the database has sent the required number of rows, the database system will discard the remaining query. In the above case, if the user only needs to return the top 50 students in the total score, the database only returns 50 records, and then ends the query job.

But in some specific circumstances, the user may still need to continue the query? If a user queries certain specific records, the user also needs to know the total number of records. How can this problem be solved? For example, you need to know the top 50 students and the total number of students whose total score is over 500. In this case, the use of the Limit clause alone may not meet your needs, because it only cares about the first 50 records. To meet this requirement, you often need to combine the SQL _calc_found_rows keyword.

This keyword is mainly used to prepare the number of records that meet the Where Condition Statement for the database administrator during the query. Then, after executing a Select Found_ROWS statement, you can obtain the total number of records that meet the conditions. However, it should be noted that using this keyword will bring some side effects. That is, a query statement with this keyword cannot use data cache. Therefore, data query performance may be reduced in some cases. Therefore, this keyword is only used when the Where clause is complex. Of course, this is just a performance consideration, not a technical limitation. That is, even if the Where clause is not complex, you can use this keyword without syntax errors. But its performance is not ideal.

Suggestion 4: special phenomena used together with the Distinct keyword

The Distinct keyword is mainly used to filter duplicate records. The Limit keyword is mainly used to specify the number of rows returned by the record. What happens if these two keywords are used together? If you understand it literally, the database returns the specified number of records that are not repeated. For example, if the Limit parameter is 50, the database returns 50 records that are not repeated. Then the subsequent query will stop. If there are duplicate records in the query records, the actual number of database queries is usually more than the number specified by the Limit keyword.

In actual work, this statement is still very useful. For example, there is a form of employee attendance information. The database administrator needs to count the number of employees in the top 20 of the number of absences. To prevent repeated records, you can add a Distinct keyword to the query statement to filter the number of repeated records. This avoids the use of multiple query statements to meet this requirement.

Recommendation 5: Relationship between Limit and Index

If the database administrator decides to use the Limit clause to specify the number of records to be displayed, it is best to use the index to the maximum extent to avoid full table scanning and improve work efficiency. That is, when the database chooses to perform a full table scan, the index can be used in some cases.

For example, the database administrator decides to use the Limit clause with the Order BY clause. Once the database finds the first RowCount row of the sorting result, the system stops sorting and does not sort the entire table. If the Order By clause is used separately, the entire table is sorted. However, sorting is a waste of time. If the database administrator decides to use the index, the query efficiency can be greatly improved.

For this content, I want to emphasize a problem. If File Sorting is required, all matching queries must be selected, and most of their content must be sorted before determining that the first row has been found. It is particularly important to note that, in any case, once a row is found, no other part of the sorting result is required, and the database automatically stops sorting.

The essential function of the Limit clause is to Limit the number of user records. However, it has many other purposes. For example, you can quickly determine the validity of the query statement and the space required to calculate the table. However, it also has some side effects, which may bring some negative effects to system operation. At this time, it is best to take some measures to improve the system performance.