Bug #118206 The optimizer has chosen a poor execution plan.
Submitted: 15 May 13:21 Modified: 19 May 21:21
Reporter: hongjun xiao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.42 OS:Ubuntu (8.0.42-0ubuntu0.20.04.1)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: hint, JOIN_ORDER

[15 May 13:21] hongjun xiao 
Description:
Plan 1 is the plan selected by the optimizer, and its cost and execution time are much higher than Plan 2

mysql> explain analyze SELECT t4.c9 AS ref0, t2.c0 AS ref1, t5.c12 AS ref2, t5.c11 AS ref3, t2.c8 AS ref4, t1.c6 AS ref5, t0.c3 AS ref6, t3.c10 AS ref7, t4.c11 AS ref8, t1.c10 AS ref9, t5.c8 AS ref10, t3.c7 AS ref11, t2.c3 AS ref12, t1.c5 AS ref13, t5.c1 AS ref14, t2.c9 AS ref15, t5.c6 AS ref16, t4.c2 AS ref17, t5.c0 AS ref18, t3.c4 AS ref19, t3.c9 AS ref20, t3.c6 AS ref21 FROM t1, t3, t5, t2 INNER JOIN t0 ON EXISTS
(SELECT 1) STRAIGHT_JOIN t4 ON IF(CAST("" AS SIGNED), (t0.c1) NOT IN (t4.c3), ('MT') && (NULL));

| -> Filter: (0 <> if(<cache>(cast('' as signed)),(t0.c1 <> t4.c3),<cache>(((0 <> 'MT') and (0 <> NULL)))))  (cost=21099 rows=153600) (actual time=75.4..75.4 rows=0 loops=1)
    -> Inner hash join (no condition)  (cost=21099 rows=153600) (actual time=21.5..69.1 rows=153600 loops=1)
        -> Table scan on t4  (cost=0.00512 rows=3) (actual time=0.0197..0.0336 rows=3 loops=1)
        -> Hash
            -> Inner hash join (no condition)  (cost=5478 rows=51200) (actual time=1.07..7.65 rows=51200 loops=1)
                -> Table scan on t2  (cost=0.00471 rows=16) (actual time=0.00886..0.0786 rows=16 loops=1)
                -> Hash
                    -> Inner hash join (no condition)  (cost=344 rows=3200) (actual time=0.107..0.526 rows=3200 loops=1)
                        -> Table scan on t3  (cost=0.0124 rows=16) (actual time=0.00563..0.0306 rows=16 loops=1)
                        -> Hash
                            -> Inner hash join (no condition)  (cost=23.2 rows=200) (actual time=0.0564..0.0746 rows=200 loops=1)
                                -> Table scan on t1  (cost=0.0656 rows=10) (actual time=0.00557..0.00675 rows=10 loops=1)
                                -> Hash
                                    -> Inner hash join (no condition)  (cost=2.9 rows=20) (actual time=0.0375..0.0421 rows=20 loops=1)
                                        -> Table scan on t5  (cost=0.188 rows=5) (actual time=0.00491..0.00791 rows=5 loops=1)
                                        -> Hash
                                            -> Table scan on t0  (cost=0.65 rows=4) (actual time=0.0213..0.0237 rows=4 loops=1)
 |

mysql> explain analyze SELECT /*+ JOIN_ORDER(t2, t5, t1) */ t4.c9 AS ref0, t2.c0 AS ref1, t5.c12 AS ref2, t5.c11 AS ref3, t2.c8 AS ref4, t1.c6 AS ref5, t0.c3 AS ref6, t3.c
5.c1 AS ref14, t2.c9 AS ref15, t5.c6 AS ref16, t4.c2 AS ref17, t5.c0 AS ref18, t3.c4 AS ref19, t3.c9 AS ref20, t3.c6 AS ref21 FROM t1, t3, t
5, t2 INNER JOIN t0 ON EXISTS (SELECT 1) STRAIGHT_JOIN t4 ON IF(CAST("" AS SIGNED), (t0.c1) NOT IN (t4.c3), ('MT') && (NULL));

| -> Inner hash join (no condition)  (cost=16543 rows=153600) (actual time=0.126..0.126 rows=0 loops=1)
    -> Table scan on t3  (cost=0.0095 rows=16) (never executed)
    -> Hash
        -> Inner hash join (no condition)  (cost=1093 rows=9600) (actual time=0.123..0.123 rows=0 loops=1)
            -> Table scan on t1  (cost=0.0106 rows=10) (never executed)
            -> Hash
                -> Inner hash join (no condition)  (cost=124 rows=960) (actual time=0.119..0.119 rows=0 loops=1)
                    -> Table scan on t5  (cost=0.0101 rows=5) (never executed)
                    -> Hash
                        -> Filter: (0 <> if(<cache>(cast('' as signed)),(t0.c1 <> t4.c3),<cache>(((0 <> 'MT') and (0 <> NULL)))))  (cost=26.8 rows=192) (actual time=0.116..0.116 rows=0 loops=1)
                            -> Inner hash join (no condition)  (cost=26.8 rows=192) (actual time=0.0651..0.101 rows=192 loops=1)
                                -> Table scan on t4  (cost=0.00957 rows=3) (actual time=0.00362..0.00583 rows=3 loops=1)
                                -> Hash
                                    -> Inner hash join (no condition)  (cost=7.3 rows=64) (actual time=0.0381..0.0497 rows=64 loops=1)
                                        -> Table scan on t2  (cost=0.463 rows=16) (actual time=0.00581..0.0132 rows=16 loops=1)
                                        -> Hash
                                            -> Table scan on t0  (cost=0.65 rows=4) (actual time=0.0207..0.023 rows=4 loops=1)
 |

How to repeat:
CREATE TABLE t0(c0 FLOAT ZEROFILL  STORAGE MEMORY COMMENT 'asdf' , c1 FLOAT  UNIQUE COLUMN_FORMAT DEFAULT PRIMARY KEY NOT NULL STORAGE MEMORY, c2 FLOAT    STORAGE MEMORY COMMENT 'asdf'  COLUMN_FORMAT DEFAULT UNIQUE KEY, c3 FLOAT   STORAGE MEMORY COLUMN_FORMAT DYNAMIC, c4 BIGINT(79) ZEROFILL , c5 FLOAT ZEROFILL  STORAGE DISK  NULL UNIQUE COMMENT 'asdf' , c6 BIGINT  COLUMN_FORMAT DYNAMIC UNIQUE STORAGE DISK  COMMENT 'asdf'  NULL, c7 DECIMAL  NULL, c8 LONGTEXT  COMMENT 'asdf'  STORAGE MEMORY NULL, c9 DECIMAL ZEROFILL  STORAGE MEMORY COLUMN_FORMAT DYNAMIC COMMENT 'asdf'  NULL , c10 INT ) ;
CREATE TABLE t1 LIKE t0;
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3(c0 FLOAT  UNIQUE  COMMENT 'asdf'  COLUMN_FORMAT FIXED STORAGE DISK , c1 FLOAT ZEROFILL  UNIQUE KEY COMMENT 'asdf' , c2 INT  UNIQUE KEY STORAGE MEMORY   COMMENT 'asdf' , c3 FLOAT ZEROFILL  COLUMN_FORMAT FIXED UNIQUE KEY   COMMENT 'asdf'  STORAGE MEMORY, c4 SMALLINT   STORAGE DISK UNIQUE COLUMN_FORMAT DYNAMIC , c5 TINYTEXT  COLUMN_FORMAT DYNAMIC STORAGE DISK NULL, c6 FLOAT , c7 LONGTEXT  STORAGE MEMORY COLUMN_FORMAT FIXED  COMMENT 'asdf' , c8 VARCHAR(500) , c9 BIGINT(83)  STORAGE MEMORY  COMMENT 'asdf'  , c10 VARCHAR(500) ) ;
CREATE TABLE IF NOT EXISTS t4(c0 TEXT  COMMENT 'asdf'  COLUMN_FORMAT DYNAMIC, c1 TINYTEXT  COMMENT 'asdf'  COLUMN_FORMAT DYNAMIC, c2 TINYINT  UNIQUE KEY PRIMARY KEY STORAGE MEMORY NOT NULL COMMENT 'asdf'  COLUMN_FORMAT DEFAULT, c3 FLOAT   COMMENT 'asdf'  COLUMN_FORMAT DYNAMIC STORAGE MEMORY , c4 DECIMAL  STORAGE MEMORY UNIQUE, c5 DECIMAL  STORAGE DISK COLUMN_FORMAT DYNAMIC  COMMENT 'asdf'  UNIQUE KEY, c6 DECIMAL , c7 FLOAT ZEROFILL    STORAGE DISK, c8 FLOAT  UNIQUE KEY, c9 TEXT , c10 FLOAT  UNIQUE KEY COMMENT 'asdf'  COLUMN_FORMAT FIXED  NULL STORAGE MEMORY, c11 DECIMAL   NULL COLUMN_FORMAT DEFAULT UNIQUE COMMENT 'asdf' , c12 INT(73) ZEROFILL  UNIQUE KEY  STORAGE DISK COLUMN_FORMAT DEFAULT COMMENT 'asdf' ) ;
CREATE TABLE IF NOT EXISTS t5 LIKE t4;
INSERT LOW_PRIORITY IGNORE INTO t1 (c3) VALUES (NULL);
INSERT LOW_PRIORITY IGNORE INTO t5 (c6, c12, c1, c4, c0, c9) VALUES (NULL, NULL, NULL, '-1712827466', NULL, 127912041);
REPLACE DELAYED INTO t2(c10, c6, c7, c9, c2, c4, c1, c3, c0, c5, c8) VALUES(NULL, 1428021853, -621412982, 978404184, 1070445330, NULL, 0.6571966211727791, 0.09886180636627218, NULL, 1346601531, -5.54563407E8);
ALTER TABLE t2 STATS_PERSISTENT DEFAULT, DELAY_KEY_WRITE 0, DROP c1, FORCE, STATS_AUTO_RECALC DEFAULT, ALGORITHM DEFAULT;
INSERT DELAYED IGNORE INTO t2 (c7, c5, c3, c2, c4) VALUES (-1490771733, 0.8399819786230822, 'Y&5G', 0.43167294750482044, 0.12665820894779678);
INSERT HIGH_PRIORITY IGNORE INTO t2 (c8, c9) VALUES (567569539, 1029341466), (0.2901762019067322, 0.9401779691545753), (NULL, 206803764);
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE 'database0';
INSERT IGNORE INTO t1(c8, c2, c9, c6, c4, c0, c3, c7) VALUES(NULL, "v!A2", -480886367, 0.9703019300113719, 0.5077273557032961, "8\r", 0.29639176212263374, NULL);
INSERT IGNORE INTO t1 (c5, c0, c1, c4, c3, c8) VALUES (NULL, '0.2901762019067322', 1.2208896148235837E-4, "t ~F2<L", '#M	4FH', 605276529);
REPLACE DELAYED INTO t2(c5, c6, c10, c3) VALUES(1235487093, '-1261136596', NULL, 0.8840122297458023);
INSERT DELAYED IGNORE INTO t4 (c12, c5, c8, c0, c9, c10, c2, c11, c6) VALUES (2095792716, NULL, NULL, 775316931, 1599352931, NULL, 1330619921, NULL, '#jy\r{TD*');
INSERT LOW_PRIORITY IGNORE INTO t5 (c4, c7, c1, c11, c3, c8, c2, c5) VALUES (-662551184, 0.5224816564008069, "1528303241", NULL, 433595449, NULL, -2.084356507E9, 0.7708435772354485);
REPLACE LOW_PRIORITY INTO t5(c6, c3, c12, c4, c10, c11, c0, c8, c5, c7, c2) VALUES(2134590435, -949247578, '1084587453', -1136207109, -1312037698, 334684704, 1787376056, 0.04554456506161986, NULL, NULL, 0.18839782600525756);
REPLACE INTO t2(c2) VALUES(NULL);
INSERT HIGH_PRIORITY IGNORE INTO t1 (c9, c8, c4, c0, c6, c7, c3, c2, c5) VALUES (NULL, NULL, -244344320, "-1068002491", NULL, 'yJ', NULL, -125042686, 38233911), ("0.22751431062455363", NULL, NULL, 385155393, NULL, 0.4452952294419482, NULL, 1554105971, 0.33), (0.6847125746290519, "", 'A~quNB曍}', '<n]|', "", NULL, "", -662551184, 0.17476666765617221);
INSERT IGNORE INTO t3 (c9, c1, c10, c6, c4, c7, c3, c5, c2, c8, c0) VALUES ('.悳q	', 0.3309158568613225, "0~(u2ZSo", -1404332739, 1812323217, 0.7699548854645899, NULL, 0.4499178836233563, NULL, NULL, 0.9882271736160763), ("-1136207109", 0.34428654212446064, "0.9933849834076292", NULL, -7.43199914E8, 0.44328622603089374, ',', 595861003, NULL, 0.748, 0.8318020475118285), (0.10721327201309183, "", 1991916190, 'fLNq', ",sz孠⪢ﾈ", 0.13098913323483585, -4.80886367E8, '-1287731105', NULL, 1246109212, -1567074289);
INSERT DELAYED IGNORE INTO t5 (c0, c2, c9, c1, c12, c10, c3, c8) VALUES (670654525, '5Qr^R}p[C', 0.2763400531798109, NULL, 714117827, 1622267473, 1038261097, 2124240155);
ANALYZE LOCAL TABLE t5, t4;
INSERT LOW_PRIORITY IGNORE INTO t5 (c11, c5, c7, c8, c4, c3, c6, c12, c0, c2, c9, c10, c1) VALUES (0.29639176212263374, 653286892, 1177642701, 0.644805672276843, 1339432993, 1397699024, -1138800700, NULL, 0.4664803041158816, 1544606542, "", 1888315812, NULL);
INSERT IGNORE INTO t3 (c5, c4, c2, c10, c9, c6, c1, c3, c8, c0) VALUES (904910542, "[ᾷ8i", 1.159519515E9, 0.44253132664685246, NULL, 'V!A2', "w9*gE~P?", 0.8105045658466156, "vI", -2027587292);
INSERT LOW_PRIORITY INTO t3 (c3, c5, c8) VALUES (1414990845, NULL, '1591179513');
REPLACE LOW_PRIORITY INTO t3(c5, c10, c4, c7, c8, c1, c0) VALUES('', NULL, NULL, '', NULL, 0.6091941887858134, 0.16887348062252783);
INSERT IGNORE INTO t1 (c1, c4, c8, c0, c5) VALUES (479496892, 0.9966263581035122, NULL, "1504853252", 0.9401779691545753);
INSERT LOW_PRIORITY IGNORE INTO t1 (c7, c2, c0, c1, c9, c6) VALUES (0.007, 0.9898329429764994, 0.0033354878771083563, -372609178, -1339331740, NULL);
INSERT IGNORE INTO t5 (c0, c12, c4, c2, c3, c6, c5, c9, c10, c8) VALUES ('374039522', 0.285641866941863, "", -613238909, "Rt1", NULL, 646094923, NULL, 2092361327, '-743199914'), (1441498921, 0.7780025044993104, '\rt	''6u슂s', '-966889341', NULL, 1.330619921E9, '', 1450399742, NULL, "⌞?"), (-1579724685, 0.27256542179634735, "guuv?5.(", 0.9187749098708679, 1.27912041E8, NULL, 1.235487093E9, NULL, -1296803138, -966889341);
INSERT LOW_PRIORITY IGNORE INTO t5 (c10, c12, c7, c4, c9, c11, c8, c6, c1, c2, c5) VALUES (0.04845722561556309, 0.9567228012759794, -1381211158, -1, 0.9298252510221163, NULL, 1010732625, '1177642701', 0.6841420720971367, 601608998, "[ᾷ8i");
REPLACE DELAYED INTO t1(c1, c8, c5) VALUES(1683556541, NULL, 1303047794);
INSERT IGNORE INTO t2 (c9, c4, c5, c3, c2, c8, c0, c7) VALUES (NULL, '', -1786692698, "", NULL, -690600709, -1133383113, ''), (NULL, "Z0}~rLG]", 0.7976267612485246, '', 0.8151649450290259, 0.6339821566177698, NULL, 0.47705634392986473), ("-137985847", 3.34684704E8, 0.5504702636455466, "", 0.4390304952550108, 0.899373774548221, 1312017269, 18711666);
INSERT HIGH_PRIORITY IGNORE INTO t4 (c12, c2, c10, c1) VALUES ('1726805229', -523641656, NULL, 0.8029636821066345);
INSERT DELAYED IGNORE INTO t2 (c2, c5, c8, c4, c0, c7, c3, c9) VALUES (NULL, 0.4040089484507956, 0.5793175355807002, 0.6918283774943073, 0.43558471747832184, "", NULL, 0.4012431020601195);
INSERT HIGH_PRIORITY IGNORE INTO t4 (c8, c1, c11, c3, c7, c6, c10, c2) VALUES ('?>', 0.9696041538128666, "⎺n", '#^', 0.8103717093297768, "", 2054875198, NULL);
INSERT IGNORE INTO t0 (c6, c2, c8, c1, c5, c4, c10, c0, c3) VALUES (0.9529150218580018, "J7sG #", -2071536264, -64143386, 0.42842616332254013, NULL, -1092987463, -674317581, NULL);
REPLACE LOW_PRIORITY INTO t2(c6, c4, c3, c9, c5, c2, c8) VALUES(NULL, NULL, 0.294253931359427, 0.5071652599013209, NULL, NULL, NULL);
INSERT IGNORE INTO t2 (c8, c9) VALUES (0.24368572012270673, 1557706913);
INSERT INTO t3 (c5, c7) VALUES (98008951, "ꪑ5p-ꒊQ"), (0.14790777098892305, "?"), (-244344320, 1560757108);
REPLACE DELAYED INTO t3(c2, c7) VALUES(NULL, NULL), ("1312017269", NULL), (1832360771, '10kA0孠G');
REPLACE INTO t1(c8, c1) VALUES(31726309, 1320103993), (0.4664803041158816, 0.39885386256708555), ('', '-413792991');
INSERT DELAYED IGNORE INTO t0 (c9, c8, c1, c3, c4) VALUES (-556906670, '', '', 0.8578421246873303, NULL);
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE 'database0';
INSERT HIGH_PRIORITY IGNORE INTO t1 (c6, c7, c10, c4, c5, c8, c9) VALUES (NULL, 1.27912041E8, "", NULL, '1599352931', -1307469360, -1635130298);
REPLACE INTO t1(c9, c1) VALUES(1.346601531E9, 2134590435);
INSERT LOW_PRIORITY IGNORE INTO t1 (c5, c3, c2, c0, c1, c10, c9, c8, c7, c6) VALUES (NULL, 0.24655486714653319, '', "s}8㹥7Vp>", -1579724685, 567569539, 1.029341466E9, -8.85609016E8, 0.6949156446798439, 0.3414077244524245);
INSERT LOW_PRIORITY IGNORE INTO t3 (c5, c9, c4, c10, c0, c8) VALUES (4.52791429E8, -1381211158, 0.24607864896347753, '366079415', "366079415", "");
INSERT LOW_PRIORITY IGNORE INTO t5 (c7) VALUES (NULL);
INSERT HIGH_PRIORITY IGNORE INTO t2 (c0, c9, c8, c10, c4) VALUES ('t{M', 0.9703019300113719, 745724928, "Ҫ57a", NULL);
INSERT INTO t0 (c7, c1) VALUES (NULL, 0.7963299504656856);
REPLACE DELAYED INTO t2(c0, c9, c7, c2) VALUES(0.29639176212263374, NULL, 0.644805672276843, -1845626723);
INSERT IGNORE INTO t5 (c2, c10, c3, c8, c11, c12, c7, c9, c4, c0, c6, c5, c1) VALUES (1203382405, -1.468015293E9, '*iaQ', 0.04096215983659668, 1439022238, 0.21883819995465237, 0.8399819786230822, -1188980421, NULL, 0.5565571862438213, 410873212, NULL, NULL);
INSERT DELAYED IGNORE INTO t3 (c0, c7, c5) VALUES (0.9096418445204365, NULL, -316660552);
REPLACE LOW_PRIORITY INTO t3(c10, c6, c2) VALUES(-1088756869, -719671609, 600772558);
INSERT INTO t2 (c4, c0) VALUES (NULL, 19636509);
INSERT IGNORE INTO t0 (c1, c9, c2, c7, c5, c4, c0, c10, c6, c8, c3) VALUES (1214307270, 0.27892278270283, '+]', 'sjd', 0.5186261065663856, 0.8922463464856415, -2099318524, "1^", 567569539, -1557727293, '1901610672');
INSERT DELAYED IGNORE INTO t0 (c1, c2) VALUES (0.23471391742664327, -7.49253763E8), (">j", 0.22762774163726351), ('w', "");
REPLACE INTO t3(c10, c8, c3, c5) VALUES(0.4748169228948754, 2.134590435E9, NULL, -1371099096);
INSERT IGNORE INTO t4 (c10, c9, c2, c5, c3, c0, c12, c1, c4) VALUES (0.10031372381800396, 1.346601531E9, 0, 0.5594180300212201, 0.7138800099162989, -436502523, 959090587, 0.3531329464942009, -411033991);
INSERT IGNORE INTO t4 (c0, c7, c11, c9, c4, c8) VALUES (264960780, 334684704, NULL, -1712827466, 'Lb', NULL), (672687100, 1100709652, 0.8265935682448796, NULL, NULL, 0.818151176484107), (0.2707242534295824, 1706691406, 789061682, ' <', 0.21152077144782078, NULL);
REPLACE LOW_PRIORITY INTO t3(c5, c10, c1, c2, c6, c7, c9) VALUES(NULL, "", 0.7230340089013703, -295347358, "-1261136596", 0.31113326208513004, NULL);
INSERT INTO t3 (c6, c0, c2, c3) VALUES (NULL, -0, NULL, 0.4902938399637563);
INSERT HIGH_PRIORITY IGNORE INTO t1 (c9, c7, c5) VALUES (0.16035483196499822, 0.1233131863990633, 0.7804275550414281), ("?0J.", NULL, 0.9666481114876839), ('452791429', 0.5769027216086051, 1113965200);

--SELECT t4.c9 AS ref0, t2.c0 AS ref1, t5.c12 AS ref2, t5.c11 AS ref3, t2.c8 AS ref4, t1.c6 AS ref5, t0.c3 AS ref6, t3.c10 AS ref7, t4.c11 AS ref8, t1.c10 AS ref9, t5.c8 AS ref10, t3.c7 AS ref11, t2.c3 AS ref12, t1.c5 AS ref13, t5.c1 AS ref14, t2.c9 AS ref15, t5.c6 AS ref16, t4.c2 AS ref17, t5.c0 AS ref18, t3.c4 AS ref19, t3.c9 AS ref20, t3.c6 AS ref21 FROM t1, t3, t5, t2 INNER JOIN t0 ON EXISTS (SELECT 1) STRAIGHT_JOIN t4 ON IF(CAST("" AS SIGNED), (t0.c1) NOT IN (t4.c3), ('MT') && (NULL))
--SELECT /*+ JOIN_ORDER(t2, t5, t1) */ t4.c9 AS ref0, t2.c0 AS ref1, t5.c12 AS ref2, t5.c11 AS ref3, t2.c8 AS ref4, t1.c6 AS ref5, t0.c3 AS ref6, t3.c10 AS ref7, t4.c11 AS ref8, t1.c10 AS ref9, t5.c8 AS ref10, t3.c7 AS ref11, t2.c3 AS ref12, t1.c5 AS ref13, t5.c1 AS ref14, t2.c9 AS ref15, t5.c6 AS ref16, t4.c2 AS ref17, t5.c0 AS ref18, t3.c4 AS ref19, t3.c9 AS ref20, t3.c6 AS ref21 FROM t1, t3, t5, t2 INNER JOIN t0 ON EXISTS (SELECT 1) STRAIGHT_JOIN t4 ON IF(CAST("" AS SIGNED), (t0.c1) NOT IN (t4.c3), ('MT') && (NULL))
[19 May 21:21] MySQL Verification Team 
Hi,

This is just too little data to be able to compare execution plans. I cannot reproduce the problem with a lot more data.