How to optimize union all in MySQL5.7
It is believed that many inexperienced people have no idea about how to optimize union all in MySQL5.7. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
In MySQL5.6, using union all is equivalent to creating a temporary table, which increases the cost of executing large federated queries and slows down the query speed.
For example, execute the following SQL statement:
(select id from accessLog order by id) union all (select id from access_test order by id)
In a MySQL5.6 environment:
Click (here) to collapse or open
Mysql > select version ()
| | version () |
| | 5.6.14-log |
1 row in set (0.00 sec)
Mysql > explain (select id from accessLog order by id) union all (select id from access_test order by id)
Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| | 1 | PRIMARY | accessLog | index | NULL | loginuserId | 9 | NULL | 535513 | Using index |
| | 2 | UNION | access_test | index | NULL | idx_loginuid | 9 | NULL | 477248 | Using index |
| | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
You can see that the temporary table created is withdrawn in the execution plan.
In a MySQL5.7 environment:
Click (here) to collapse or open
Mysql > select version ()
| | version () |
| | 5.7.18-log |
1 row in set (0.00 sec)
Mysql > explain (select id from accessLog order by id) union all (select id from access_test order by id)
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| | 1 | PRIMARY | accessLog | NULL | index | NULL | loginuserId | 9 | NULL | 586090 | 100.00 | Using index |
| | 2 | UNION | access_test | NULL | ALL | NULL | NULL | NULL | NULL | 571023 | 100.00 | NULL |
No temporary table is created in the whole query process. According to the order, the query results of accessLog table are first transferred to the client, and then the query results of access_ test table are transferred to the client.
Note: this optimization is not valid for union and order by for outermost use, as follows:
Click (here) to collapse or open
Mysql > select version ()
| | version () |
| | 5.7.18-log |
1 row in set (0.00 sec)
Mysql > explain (select id from accessLog order by id) union all (select id from access_test order by id) order by id
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| | 1 | PRIMARY | accessLog | NULL | index | NULL | loginuserId | 9 | NULL | 586090 | 100.00 | Using index |
| | 2 | UNION | access_test | NULL | ALL | NULL | NULL | NULL | NULL | 571023 | 100.00 | NULL |
| | NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
After reading the above, have you mastered how to optimize union all in MySQL5.7? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
