PS 8.0 PERFORMANCE REGRESSION IN SELECT DISTINCT
General
Escalation
General
Escalation
Description
Environment
None
AFFECTED CS IDs
275749
Smart Checklist
Activity
Show:
George Lorch December 20, 2021 at 7:40 PM
[25 May 2020 11:17] Erlend Dahl
Already fixed in the upcoming 8.0.21 release under the heading of
Bug#30562964 8.0.18: PERFORMANCE REGRESSION IN SELECT DISTINCTLalit Choudhary June 1, 2020 at 2:37 PM
Upstream update:
Already fixed in the upcoming 8.0.21 release under the heading of
Bug#30562964 8.0.18: PERFORMANCE REGRESSION IN SELECT DISTINCT
Done
Details
Details
Assignee
Unassigned
UnassignedReporter
Lalit Choudhary
Lalit ChoudharyLabels
Fix versions
Affects versions
Priority
MediumSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created June 1, 2020 at 2:30 PM
Updated March 6, 2024 at 11:07 AM
Resolved December 20, 2021 at 7:40 PM
Issue: PERFORMANCE REGRESSION IN SELECT DISTINCT ( using temporary) in the 8.0 version.
Steps to reproduce:
Test case and data ref: https://bugs.mysql.com/bug.php?id=99593
Table count with same data and default configuration PS-5.7.29 vs PS-8.0.19
mysql [localhost] {msandbox} (test) > select count(*) from oc_product_to_store; +----------+ | count(*) | +----------+ | 2641426 | +----------+ 1 row in set (0.35 sec) mysql [localhost] {msandbox} (test) > select count(*) from oc_product_to_vehicle; +----------+ | count(*) | +----------+ | 18330148 | +----------+ 1 row in set (2.94 sec)PS-5.7.29
SELECT DISTINCT vehicle_id, submodel_id, store_id FROM oc_product_to_store pts JOIN oc_product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21; | 15417 | 20 | 21 | | 15431 | 20 | 21 | | 15432 | 20 | 21 | | 5537 | 1258 | 21 | +------------+-------------+----------+ 60193 rows in set (5.31 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT DISTINCT vehicle_id, submodel_id, store_id FROM oc_product_to_store pts JOIN oc_product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pts partitions: NULL type: ref possible_keys: PRIMARY,product_id,store_id,store_product key: store_id key_len: 4 ref: const rows: 755300 filtered: 100.00 Extra: Using index; Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ptv partitions: NULL type: ref possible_keys: product_vehicle_submodel,vehicle_product key: product_vehicle_submodel key_len: 4 ref: test.pts.product_id rows: 32 filtered: 50.00 Extra: Using where; Using index 2 rows in set, 1 warning (0.01 sec)PS-8.0.19
SELECT DISTINCT vehicle_id, submodel_id, store_id FROM oc_product_to_store pts JOIN oc_product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21; | 15417 | 20 | 21 | | 15431 | 20 | 21 | | 15432 | 20 | 21 | | 5537 | 1258 | 21 | +------------+-------------+----------+ 60193 rows in set (13.18 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT DISTINCT vehicle_id, submodel_id, store_id FROM oc_product_to_store pts JOIN oc_product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pts partitions: NULL type: ref possible_keys: PRIMARY,product_id,store_id,store_product key: store_id key_len: 4 ref: const rows: 755300 filtered: 100.00 Extra: Using index; Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ptv partitions: NULL type: ref possible_keys: product_vehicle_submodel,vehicle_product key: product_vehicle_submodel key_len: 4 ref: test.pts.product_id rows: 32 filtered: 50.00 Extra: Using where; Using index 2 rows in set, 1 warning (0.00 sec)Workaround:
PS-5.7.29
| 15431 | 20 | 21 | | 15432 | 20 | 21 | | 5537 | 1258 | 21 | +------------+-------------+----------+ 60193 rows in set (5.24 sec)PS-8.0.19 with SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;
| 15416 | 20 | 21 | | 15417 | 20 | 21 | | 15431 | 20 | 21 | | 15432 | 20 | 21 | | 5537 | 1258 | 21 | +------------+-------------+----------+ 60193 rows in set (5.92 sec)