Bug #115676 The optimizer has chosen a poor execution plan.
Submitted: 24 Jul 2024 4:34 Modified: 24 Jul 2024 9:31
Reporter: hongjun xiao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.37, 8.0.39 OS:Any
Assigned to: CPU Architecture:Any
Tags: BKA, cost, Optimizer hints

[24 Jul 2024 4:34] hongjun xiao 
Description:
Potential performance bugs in the optimizer, sometimes it chooses a plan with higher cost and execution time.This potential performance bug could lead to more serious issues.
This is a simple use caseļ¼š

mysql> explain analyze SELECT DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
| -> Table scan on <temporary>  (cost=153..165 rows=720) (actual time=0.0186..0.0186 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=153..153 rows=720) (actual time=0.0181..0.0181 rows=0 loops=1)
        -> Inner hash join (no condition)  (cost=81.5 rows=720) (actual time=0.0121..0.0121 rows=0 loops=1)
            -> Table scan on t2  (cost=0.0174 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (cost=9.2 rows=72) (actual time=0.0108..0.0108 rows=0 loops=1)
                    -> Table scan on t4  (cost=0.144 rows=9) (never executed)
                    -> Hash
                        -> Inner hash join (no condition)  (cost=1.75 rows=8) (actual time=0.00945..0.00945 rows=0 loops=1)
                            -> Table scan on t0  (cost=1.05 rows=8) (never executed)
                            -> Hash
                                -> Nested loop inner join  (cost=0.7 rows=1) (actual time=0.00563..0.00563 rows=0 loops=1)
                                    -> Filter: (t5.c0 is not null)  (cost=0.35 rows=1) (actual time=0.00523..0.00523 rows=0 loops=1)
                                        -> Table scan on t5  (cost=0.35 rows=1) (actual time=0.00469..0.00469 rows=0 loops=1)
                                    -> Filter: (t3.c2 = t5.c2)  (cost=0.35 rows=1) (never executed)
                                        -> Single-row index lookup on t3 using c0 (c0=t5.c0), with index condition: (t3.c0 = t5.c0)  (cost=0.35 rows=1) (never executed)
 |

mysql> explain analyze SELECT /*+ BKA()*/ DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;

| -> Table scan on <temporary>  (cost=2.5..2.5 rows=0) (actual time=0.015..0.015 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=0..0 rows=0) (actual time=0.0146..0.0146 rows=0 loops=1)
        -> Inner hash join (no condition)  (actual time=0.0108..0.0108 rows=0 loops=1)
            -> Table scan on t2  (cost=0.0174 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (actual time=0.00963..0.00963 rows=0 loops=1)
                    -> Table scan on t4  (cost=0.144 rows=9) (never executed)
                    -> Hash
                        -> Inner hash join (no condition)  (actual time=0.00842..0.00842 rows=0 loops=1)
                            -> Table scan on t0  (cost=1.05 rows=8) (never executed)
                            -> Hash
                                -> Batched key access inner join  (actual time=0.00659..0.00659 rows=0 loops=1)
                                    -> Batch input rows
                                        -> Filter: (t5.c0 is not null)  (cost=0.35 rows=1) (actual time=0.00575..0.00575 rows=0 loops=1)
                                            -> Table scan on t5  (cost=0.35 rows=1) (actual time=0.00541..0.00541 rows=0 loops=1)
                                    -> Filter: ((t3.c2 = t5.c2) and (t3.c0 = t5.c0))  (cost=0.35 rows=1) (never executed)
                                        -> Multi-range index lookup on t3 using c0 (c0=t5.c0)  (cost=0.35 rows=1) (never executed)
 |

How to repeat:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

SET optimizer_switch = 'batched_key_access=on';

CREATE TABLE t0(c1 DECIMAL ZEROFILL   , c2 FLOAT UNIQUE) ;

INSERT DELAYED IGNORE INTO t0(c2, c1) VALUES(NULL, 0.27508044354375805), 
(0.33021462504457944, -1577010364), 
(-1027634489, -120016379), 
(0.148761903571631, NULL), 
(1186769424, NULL), 
(NULL, 0.9674081491026901), 
(NULL, 8.93224485E8), 
(NULL, 0.5556505882412694);

CREATE TABLE t2(c1 FLOAT) ;

INSERT LOW_PRIORITY IGNORE INTO t2(c1) VALUES("6Y^c!3Sq"), 
(-1.860810432E9), 
(NULL), 
("-1154092145"), 
('gVETKWUWGf'), 
(-1.647359993E9), 
(0.987122207266091), 
(395739774), 
("-558649976"), 
(NULL);

CREATE TABLE t3(c0 TINYINT UNIQUE KEY, c2 INT(252));

CREATE TABLE IF NOT EXISTS t4(c0 DECIMAL, c2 FLOAT, c10 DECIMAL) ;
CREATE TABLE t5 LIKE t4;

INSERT LOW_PRIORITY IGNORE INTO t4(c0) VALUES(NULL), 
(-181481389), 
(0.8927678276626669), 
("abc"), 
(NULL), 
(NULL), 
(0.09598112743422371), 
(-333972601), 
("def");

explain analyze SELECT DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
explain analyze SELECT /*+ BKA()*/ DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;

-- result:
mysql> explain analyze SELECT DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=153..165 rows=720) (actual time=0.0186..0.0186 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=153..153 rows=720) (actual time=0.0181..0.0181 rows=0 loops=1)
        -> Inner hash join (no condition)  (cost=81.5 rows=720) (actual time=0.0121..0.0121 rows=0 loops=1)
            -> Table scan on t2  (cost=0.0174 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (cost=9.2 rows=72) (actual time=0.0108..0.0108 rows=0 loops=1)
                    -> Table scan on t4  (cost=0.144 rows=9) (never executed)
                    -> Hash
                        -> Inner hash join (no condition)  (cost=1.75 rows=8) (actual time=0.00945..0.00945 rows=0 loops=1)
                            -> Table scan on t0  (cost=1.05 rows=8) (never executed)
                            -> Hash
                                -> Nested loop inner join  (cost=0.7 rows=1) (actual time=0.00563..0.00563 rows=0 loops=1)
                                    -> Filter: (t5.c0 is not null)  (cost=0.35 rows=1) (actual time=0.00523..0.00523 rows=0 loops=1)
                                        -> Table scan on t5  (cost=0.35 rows=1) (actual time=0.00469..0.00469 rows=0 loops=1)
                                    -> Filter: (t3.c2 = t5.c2)  (cost=0.35 rows=1) (never executed)
                                        -> Single-row index lookup on t3 using c0 (c0=t5.c0), with index condition: (t3.c0 = t5.c0)  (cost=0.35 rows=1) (never executed)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT /*+ BKA()*/ DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.5..2.5 rows=0) (actual time=0.015..0.015 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=0..0 rows=0) (actual time=0.0146..0.0146 rows=0 loops=1)
        -> Inner hash join (no condition)  (actual time=0.0108..0.0108 rows=0 loops=1)
            -> Table scan on t2  (cost=0.0174 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (actual time=0.00963..0.00963 rows=0 loops=1)
                    -> Table scan on t4  (cost=0.144 rows=9) (never executed)
                    -> Hash
                        -> Inner hash join (no condition)  (actual time=0.00842..0.00842 rows=0 loops=1)
                            -> Table scan on t0  (cost=1.05 rows=8) (never executed)
                            -> Hash
                                -> Batched key access inner join  (actual time=0.00659..0.00659 rows=0 loops=1)
                                    -> Batch input rows
                                        -> Filter: (t5.c0 is not null)  (cost=0.35 rows=1) (actual time=0.00575..0.00575 rows=0 loops=1)
                                            -> Table scan on t5  (cost=0.35 rows=1) (actual time=0.00541..0.00541 rows=0 loops=1)
                                    -> Filter: ((t3.c2 = t5.c2) and (t3.c0 = t5.c0))  (cost=0.35 rows=1) (never executed)
                                        -> Multi-range index lookup on t3 using c0 (c0=t5.c0)  (cost=0.35 rows=1) (never executed)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- The `ANALYZE` statements can improve this situation.
ANALYZE TABLE t0 UPDATE HISTOGRAM ON c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11;
ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10;
ANALYZE TABLE t3 UPDATE HISTOGRAM ON c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10;
ANALYZE TABLE t4 UPDATE HISTOGRAM ON c0, c1, c2, c3, c5, c6, c7, c8, c9, c10;

explain analyze SELECT DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
explain analyze SELECT /*+ BKA()*/ DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
-- result:
mysql> explain analyze SELECT DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=10..13 rows=40) (actual time=0.0282..0.0282 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=9.95..9.95 rows=40) (actual time=0.0275..0.0275 rows=0 loops=1)
        -> Inner hash join (no condition)  (cost=5.95 rows=40) (actual time=0.0218..0.0218 rows=0 loops=1)
            -> Table scan on t2  (cost=0.313 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (cost=1.7 rows=4) (actual time=0.0203..0.0203 rows=0 loops=1)
                    -> Table scan on t0  (cost=0.65 rows=4) (never executed)
                    -> Hash
                        -> Nested loop inner join  (cost=1.05 rows=1) (actual time=0.0183..0.0183 rows=0 loops=1)
                            -> Inner hash join (no condition)  (cost=0.7 rows=1) (actual time=0.0179..0.0179 rows=0 loops=1)
                                -> Filter: (t5.c0 is not null)  (cost=0.35 rows=1) (actual time=0.0024..0.0024 rows=0 loops=1)
                                    -> Table scan on t5  (cost=0.35 rows=1) (actual time=0.0022..0.0022 rows=0 loops=1)
                                -> Hash
                                    -> Table scan on t4  (cost=0.35 rows=1) (actual time=0.00632..0.00976 rows=9 loops=1)
                            -> Filter: (t3.c2 = t5.c2)  (cost=0.35 rows=1) (never executed)
                                -> Single-row index lookup on t3 using c0 (c0=t5.c0), with index condition: (t3.c0 = t5.c0)  (cost=0.35 rows=1) (never executed)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT /*+ BKA()*/ DISTINCT t0.c1 FROM t4, t2, t0, t3 NATURAL JOIN t5;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.5..2.5 rows=0) (actual time=0.0228..0.0228 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=0..0 rows=0) (actual time=0.0224..0.0224 rows=0 loops=1)
        -> Inner hash join (no condition)  (actual time=0.0185..0.0185 rows=0 loops=1)
            -> Table scan on t2  (cost=0.313 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (actual time=0.017..0.017 rows=0 loops=1)
                    -> Table scan on t0  (cost=0.65 rows=4) (never executed)
                    -> Hash
                        -> Batched key access inner join  (actual time=0.0154..0.0154 rows=0 loops=1)
                            -> Batch input rows
                                -> Inner hash join (no condition)  (cost=0.7 rows=1) (actual time=0.0145..0.0145 rows=0 loops=1)
                                    -> Filter: (t5.c0 is not null)  (cost=0.35 rows=1) (actual time=0.00218..0.00218 rows=0 loops=1)
                                        -> Table scan on t5  (cost=0.35 rows=1) (actual time=0.00199..0.00199 rows=0 loops=1)
                                    -> Hash
                                        -> Table scan on t4  (cost=0.35 rows=1) (actual time=0.00574..0.00926 rows=9 loops=1)
                            -> Filter: ((t3.c2 = t5.c2) and (t3.c0 = t5.c0))  (cost=0.35 rows=1) (never executed)
                                -> Multi-range index lookup on t3 using c0 (c0=t5.c0)  (cost=0.35 rows=1) (never executed)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[24 Jul 2024 9:31] MySQL Verification Team 
Hello hongjun xiao,

Thank you for the report and test case.

regards,
Umesh