mysqlsqlmysql-8.0
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)
Solution
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.
- How to deal with enormous line lengths created by mysqldump
- MySQL union - alias not visible
- Join newest price from price-table for all products
- Mysql ERROR: ASCII '\0' while importing sql file on linux server
- User registration with CodeIgniter
- Paging Results: Handling invalid passed data
- I'm stuck with foreign key constraint fails error. .NET 8, EF Core 8.0.2
- find number of rows in returned mysql result (nodejs)
- Get records that have total count of over X amount
- Excluding records between dates AND with an associating value
- MYSQL Update query, replace part string containing URL
- MySQL Not Equal To Certain Length
- How can I solve "Error: MySQL shutdown unexpectedly"?
- How do I turn off the mysql password validation?
- How to create table like another table with additional columns in MYSQL
- How import also column name in CSV file
- Export Column Names to CSV
- split characters and numbers in MySQL
- If only CI fails, what kind of trouble could there be for django?
- Perform an INNER JOIN with more than 2 tables in MySQL
- Imploding a list for use in a Python MySQL IN clause
- SQL check if timestamp is greater than 24 hours from now (MYSQL)
- SQL - Query to find if a string contains part of the value in Column
- MySQL stored function: CONTAINS SQL or NO SQL?
- MySQL "delayed commit ok done" and performance
- MySQL Query performance - best partial match
- MySQL: How to reset or change the MySQL root password?
- How to reset the root password in MySQL 8.0.11?
- Count 'IN' value in MySQL from multiple non-normalized columns
- coupon code statistics