Ian
Reputation: 354
the select performance issue on mysql 8.0
I have met a performance issue for the sql below.
select a.id, a.name, a.chinese_name, turnover, group_concat(b.branch) as branch,
group_concat(b.type_of_service) as service
from kn_supplier a join kn_supplier_service b
on a.id = b.supplier_id
group by a.name
It runs around 13 seconds on machine A, but almost 2 seconds on machine B. The question is both machine A and machine B have the same enviroment with MySQL 8.0. The schema, the tables, the index are all the same. Why it has so many big difference? Is there any trick on MySQL 8.0 I forgot to set? Note, the sql runs on local server.
I tried to profile both machines,
Machine A
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=7;
+----------+-----+--------------------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |
+----------+-----+--------------------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| 7 | 2 | starting | 0.000260 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 7 | 3 | Executing hook on transaction | 0.000010 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1107 |
| 7 | 4 | starting | 0.000017 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1109 |
| 7 | 5 | checking permissions | 0.000009 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2203 |
| 7 | 6 | checking permissions | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2203 |
| 7 | 7 | Opening tables | 0.000944 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | open_tables | sql_base.cc | 5590 |
| 7 | 8 | init | 0.000018 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 662 |
| 7 | 9 | System lock | 0.000025 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_lock_tables | lock.cc | 332 |
| 7 | 10 | optimizing | 0.000022 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 217 |
| 7 | 11 | statistics | 0.000048 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 429 |
| 7 | 12 | preparing | 0.000069 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 504 |
| 7 | 13 | Creating tmp table | 0.000221 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::create_intermediate_tabl | sql_executor.cc | 325 |
| 7 | 14 | executing | 12.978405 | 12.953125 | 0.015625 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::exec | sql_executor.cc | 227 |
| 7 | 15 | end | 0.000024 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 715 |
| 7 | 16 | query end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4547 |
| 7 | 17 | waiting for handler commit | 0.000313 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ha_commit_trans | handler.cc | 1570 |
| 7 | 18 | removing tmp table | 0.000257 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2279 |
| 7 | 19 | waiting for handler commit | 0.000020 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2319 |
| 7 | 20 | closing tables | 0.000032 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4593 |
| 7 | 21 | freeing items | 0.000020 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_parse | sql_parse.cc | 5264 |
| 7 | 22 | removing tmp table | 0.000010 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2279 |
| 7 | 23 | freeing items | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2319 |
| 7 | 24 | removing tmp table | 0.000013 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2279 |
| 7 | 25 | freeing items | 0.000111 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2319 |
| 7 | 26 | logging slow query | 0.000066 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | log_slow_do | log.cc | 1623 |
| 7 | 27 | cleaning up | 0.000032 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | dispatch_command | sql_parse.cc | 2159 |
+----------+-----+--------------------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
26 rows in set, 1 warning (0.00 sec)
Machine B
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=3;
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| 3 | 2 | starting | 0.000082 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | 3 | Executing hook on transaction | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1119 |
| 3 | 4 | starting | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1121 |
| 3 | 5 | checking permissions | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2218 |
| 3 | 6 | checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2218 |
| 3 | 7 | Opening tables | 0.000344 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | open_tables | sql_base.cc | 5574 |
| 3 | 8 | init | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 666 |
| 3 | 9 | System lock | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_lock_tables | lock.cc | 331 |
| 3 | 10 | optimizing | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 219 |
| 3 | 11 | statistics | 0.000020 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 439 |
| 3 | 12 | preparing | 0.000016 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 520 |
| 3 | 13 | Creating tmp table | 0.000167 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::create_intermediate_tabl | sql_executor.cc | 336 |
| 3 | 14 | executing | 0.037210 | 0.000000 | 0.031250 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SELECT_LEX_UNIT::ExecuteIterat | sql_union.cc | 1398 |
| 3 | 15 | end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 719 |
| 3 | 16 | query end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4579 |
| 3 | 17 | waiting for handler commit | 0.038665 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ha_commit_trans | handler.cc | 1569 |
| 3 | 18 | removing tmp table | 0.000176 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2305 |
| 3 | 19 | waiting for handler commit | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2345 |
| 3 | 20 | closing tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4626 |
| 3 | 21 | freeing items | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_parse | sql_parse.cc | 5299 |
| 3 | 22 | removing tmp table | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2305 |
| 3 | 23 | freeing items | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2345 |
| 3 | 24 | removing tmp table | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2305 |
| 3 | 25 | freeing items | 0.000061 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2345 |
| 3 | 26 | cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | dispatch_command | sql_parse.cc | 2172 |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
25 rows in set, 1 warning (0.00 sec)
Upvotes: 1
Views: 925
Answers (2)
lefred
Reputation: 129
As you are using MySQL 8.0, I would encourage you to check the output of EXPLAIN ANALYZE (8.0.18) of your query on both systems.
Upvotes: 0
Tim Biegeleisen
Reputation: 522516
Calls to GROUP_CONCAT involve every record in the group, with no chance to reduce the overhead there. In addition, your query has no WHERE clause. You may try adding the following index to the kn_supplier_service table:
CREATE INDEX idx on kn_supplier_service (supplier_id, branch, type_of_service);
This would at least allow MySQL to quickly lookup each id value in the kn_supplier table against the kn_supplier_service table.
Upvotes: 1
Related Questions
- SQL query much slower in MySql 8 than in MySql 5.6
- Select Query takes more excution Time
- Improve speed execution of MySQL select query
- how to improve select performance in mysql?
- Improving SELECT performance in MySQL
- optimization of mysql select (bad performance)
- Performance issues in MySQL SELECT queries?
- Slow MySQL SELECT performance
- MySQL perfomance issues in SELECT
- Slow mysql select