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

Editor to share with you how to do a good Limit optimization in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Suggestion 1: use Limit 0 clause flexibly

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. Actually this is not so. In practical work, the flexible use of this zero parameter can bring us a great harvest.

For example, now the database engineer wants to confirm the validity of a query statement. If you run the query statement directly, you need to wait for the returned record. If there are a large number of records involved, or if the operation logic is complex, it will take a long time. At this point, you can use the Limit 0 clause in the Select query statement. As long as the query statement has no syntax errors, this allows the database to quickly return an empty collection. So as to help people quickly judge the effectiveness of the query statement. In addition, the data type of each field of a table is returned in this empty set sum. That is, you can also query the table structure of a table through this Limit 0 clause.

It can be seen that the flexible application of Limir 0 clause can indeed bring us a lot of benefits. It is important to note, however, that this clause may not work in certain situations. As usual, this Limit 0 clause is not supported in a Monitor work environment. At this point, the results will only show Empty Set, not the results we need.

Recommendation 2: use Limit in conjunction with Group By

The Group By keyword is mainly used to classify and summarize data. However, before classifying and summarizing, it is often necessary to sort the advanced nature of the data. When the Limit statement is used to specify the number of results to be displayed, it often involves the classification and sorting of records. For example, in a school performance management system, it is necessary to sort the total scores of students. That is, the students' scores in each subject are summarized first, and then the record of their ranking in the top 50 is displayed. You need to use both the Group By clause and the Limit clause at this point. In fact, we can also see from this case that the two clauses are interdependent. It is precisely because of this feature (often used in conjunction with each other) that combining the Group By clause can improve the query efficiency of Limit.

This is mainly because if the two are used together, the Limit keyword will not repeat any unnecessary Group By values. In other words, in some cases, the Group By clause can solve the sorting problem by reading the key sequentially or sorting on the key, and then calculate the summary until the value of the keyword changes. In this case, some common work that the two clauses need to do can be done only once. This can be used to improve the performance of the application system from another perspective. Compared with doing a view to classify and summarize the data, and then using a query statement to extract a specific number of records, it is a little more efficient. Because the latter is to use the two clauses separately, they can not enjoy the advantages of using them together.

These are all the contents of the article "how to optimize Limit in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow 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.