Shulou(Shulou.com)06/01 Report--

This article mainly shows you "MYSQL how to optimize group by", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MYSQL how to optimize group by" this article.

Generally speaking, there is a saying that a MYSQL table with more than 5 million rows of data is not good, and after this statement, the performance of MYSQL's group by is extremely poor.

If you want to use a sentence, you use MYSQL as other databases (PG, SQL SERVER, ORACLE), the result.

Select last_name,count (*) from employees where hire_date between '1990-01-01' and '2000-01-01' group by last_name limit 10

Above is a query statement

The following is the query time with index, no index and different indexes.

In three cases, when the index is left in the grouped field, the query time is the shortest.

Because group by actually performs the same sort operation, group by is basically a sorted grouping operation, so that we can scan the data in groups and execute the groups dynamically. Therefore, in the case of conditional indexes after where and indexes of GROUP BY fields, it is highly likely that grouped indexes are selected for related queries.

Of course, we can also use some parameters to force the expected results of the system query, such as SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT.

We can see three kinds of mandatory expectations.

1. If the data result set of our group by or distinct operations is relatively large, using big_result,MYSQL will create temporary tables on disk, and will probably use full table scanning.

2 if our preset result set is relatively small, the result set will be stored in memory. You can see that even Hong Kong's file sort no longer exists.

3 if you want to unlock the query table more quickly, you can choose buffer_result, which will unlock the table and store the results on the local machine as soon as possible instead of directly send data

Let's look at a slightly more complex query.

Select d.deptdistributor no count (s.salary) as count_salary from employees as e

-> left join salaries as s on e.emp_no = s.emp_no

-> left join dept_emp as d on d.emp_no = e.emp_no

-> where e.gender ='M'

-> group by d.dept_no

The main purpose of the query is to query the total wage consumption of each department of men.

You can see how to optimize such a query in MYSQL in less than 6 seconds.

First of all, the query time is too long is a problem, sometimes our idea is how to make this statement produce results faster, and add a variety of indexes, while in practice, another idea of sentence optimization is how to make the time of locking the table shorter, it seems that the two do not contradict, but in fact, of course, they may actually be two completely different ideas.

For example, if I do this in the above statement, I first get the dept_no of all the department grouping information.

Save it in the program's cache, and then

After the total salary of each department is obtained by the following statement, after the cumulative calculation (which is not a difficult thing to do by using the program), the overall result is the same as the above statement, and after the actual operation, the longest salary of the nine departments is only 0.34 seconds, and the shortest is only 0.02 seconds. The cumulative time of the overall nine queries is less than 1 second.

Select d.deptagono count (s.salary) as count_salary from employees as e left join salaries as s on e.emp_no = s.emp_no left join dept_emp as d on d.emp_no = e.emp_no where e.gender ='M 'and d.dept_no =' d009'

Through such a query method, it is better than how to optimize a SQL as a whole. Sentence optimization must be flexible and not single-minded. Of course, if you encounter a similar situation, you have to analyze it. If you encounter GROUP BY, you will use this method. In fact, it is still a single-minded idea.

The above is all the content of the article "how MYSQL optimizes group by". 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.