Shulou(Shulou.com)05/31 Report--

In this issue, Xiaobian will bring you about how to optimize the Limit keyword. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

How to optimize the Limit keyword

Recommendation 1: Flexible use of Limit0 clause

According to the definition of the Limit keyword, if the parameter is 0, it returns an empty record. It doesn't seem to make much sense. It's not true. In practical work, flexible use of this 0 parameter can bring us great gains.

For example, if a database engineer wants to confirm the validity of a query statement, if he runs the query statement directly, he needs to wait for the record it returns. If the number of records involved is relatively large, or the operation logic is relatively complex, it will take a long time to wait. You can use the Limit0 clause in the Select query statement. This allows the database to quickly return an empty collection as long as the query statement has no syntax errors. This helps database designers to quickly judge the validity of query statements. In addition, this empty set and also returns the data type of each field of a table. That is, you can query the table structure of a table through this Limit0 clause.

It can be seen that flexible application of Limir0 clause can indeed bring us a lot of benefits. Note, however, that in certain situations this clause may not work. This Limit0 clause is not normally supported in the Monitor working environment. The result will only show EmptySet, not the result we want.

Recommendation 2: Limit combined with GroupBy

The GroupBy keyword is primarily used to summarize data. However, it is often necessary to sort the data by their advanced nature before aggregating them. Limit statements, when used to specify the number of results to be displayed, often involve problems with categorizing and sorting records. For example, in a school performance management system, the total score of students needs to be sorted. That is, the student's subject scores are summarized first, and then the top 50 records are displayed. Both the GroupBy clause and the Limit clause are required. In fact, we can see from this case that these two clauses are interdependent. It is because of this feature (often used in conjunction with each other) that the GroupBy clause can be combined to improve query efficiency for Limit.

This is mainly because the Limit keyword, if used together, will not double calculate any unnecessary GroupBy values. In other words, in some cases, the GroupBy clause can solve sorting problems in a taxonomy by reading keys sequentially or sorting on keys, and then computing the digest until the value of the keyword changes. In this case, the two clauses need to do some common work, only once. This can be used to improve application performance from another angle. It's a bit more efficient than doing a view to sort and summarize the data and then using a query to extract a specific number of records. Because the latter is used separately from the two clauses, it does not enjoy the advantages of using them together.

How to optimize the Limit keyword

Recommendation 3: Use SQL_calc_found_rows to increase clause flexibility

By default, the Limit clause returns the number of rows of records specified by the user. As soon as the database has sent the number of rows required by the user, the database system discards the remaining queries. That is, in the case of the student score above, if the user only needs to return the top 50 students with total scores, the database will only return 50 records, and then terminate the query assignment.

However, in some specific cases, users may still need to continue the follow-up query. For example, users query certain specific records, and they also need to know the total number of records. At this time, how to deal with it? For example, now users need to know the information of the top 50 students, and they need to know the total number of people with a total score of more than 500 points. The Limit clause alone may not satisfy the user's needs because it only cares about the first 50 records. To implement this requirement, it is often necessary to combine the SQL_calc_found_rows keyword.

The main purpose of this keyword is to be able to prepare the database administrator in advance the number of records that match the Where conditional statement at query time. The user can then obtain the total number of eligible records simply by executing a SelectFound_ROWS statement later. However, it should be noted that using this keyword will bring certain side effects. That is, query statements with this keyword cannot use the data cache. In some cases, this can reduce the performance of data queries. Therefore, in general, this keyword is only used Where the conditional statement is more complex. Of course, this is only a performance recommendation, not a technical limitation. That is, even if the Where conditional statement is not complex, you can use this keyword without grammatical errors. It's just not ideal in terms of performance.

Recommendation 4: Special phenomenon when used together with Distinct keyword

The Distinct keyword is primarily used to filter duplicate records. The Limit keyword is used primarily to specify the number of rows returned by the record. What happens when these two keywords are used together? Literally, the database returns the specified number of unique records. If the Limit parameter is 50, the database returns 50 unique records. Then subsequent queries will stop. If there are duplicate records in the query, the actual number of database queries tends to be greater than the number specified by the Limit keyword.

In practice, this statement is still very useful. For example, there is now a form of employee attendance information. The database administrator now needs to count the top 20 employees for absenteeism. In order to prevent duplicate records, you can add a Distinct keyword to the query statement to filter the number of duplicate records. Thus, multiple query statements can be avoided to fulfill this requirement.

Recommendation 5: Relationship between Limit and Index

If the database administrator decides to use a Limit clause to specify the number of records to display, it is best to maximize the use of indexes to avoid full table scans and improve productivity. That is, when the database chooses to do a full table scan, indexes can be used in some cases.

For example, the database administrator now decides to use the Limit clause with the OrderBY clause. Once the database finds the first RowCount row of the sorted result, the system ends sorting without sorting the entire table. If you use the OrderBy clause alone, the entire table is sorted. Even so, sorting is bound to waste some time. At this point, the database administrator can greatly improve the efficiency of this query if he decides to use indexes.

For this content, the author would like to emphasize a problem. If you must sort files, you must select all matching queries and sort most of them before you can be sure that the first row has been found. It is important to emphasize that in any case, once a row is found, there is no need to sort the rest of the result, and the database automatically ends sorting.

The Limit clause essentially limits the number of records a user can have. But it has many other uses. For example, quickly determine the validity of query statements, calculate the space required by tables, and so on. However, it also has certain side effects, which may bring some negative effects to the operation of the system. At this point, it is best to take some measures to improve the performance of the system operation.

The above is how to optimize the Limit keyword shared by Xiaobian for everyone. If you happen to have similar doubts, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.