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 DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0;
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary> (cost=3.31..3.31 rows=1) (actual time=0.0748..0.0751 rows=1 loops=1)
-> Temporary table with deduplication (cost=0.8..0.8 rows=1) (actual time=0.0739..0.0739 rows=1 loops=1)
-> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0497..0.0551 rows=1 loops=1)
-> Table scan on t2 (cost=0.35 rows=1) (actual time=0.0289..0.0337 rows=1 loops=1)
-> Filter: (t5.c1 = t2.c1) (cost=0.35 rows=1) (actual time=0.0187..0.0189 rows=1 loops=1)
-> Single-row index lookup on t5 using PRIMARY (c0=t2.c0) (cost=0.35 rows=1) (actual time=0.0165..0.0166 rows=1 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
When utilizing the query hint JOIN_SUFFIX, this can be observed.
mysql> explain analyze SELECT /*+ JOIN_SUFFIX(t2)*/ DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0;
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates) (cost=0.8 rows=1) (actual time=0.0409..0.0411 rows=1 loops=1)
-> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0333..0.0374 rows=1 loops=1)
-> Index scan on t5 using PRIMARY (cost=0.35 rows=1) (actual time=0.0206..0.0241 rows=1 loops=1)
-> Filter: (t2.c1 = t5.c1) (cost=0.35 rows=1) (actual time=0.0111..0.0114 rows=1 loops=1)
-> Single-row index lookup on t2 using PRIMARY (c0=t5.c0) (cost=0.35 rows=1) (actual time=0.01..0.0101 rows=1 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE t2(c0 INT PRIMARY KEY, c1 FLOAT ZEROFILL) ;
CREATE TABLE t5 LIKE t2;
INSERT INTO t2( c0, c1) VALUES(123, 456);
INSERT INTO t5(c0, c1) VALUES(123, 456);
ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1;
ANALYZE TABLE t5 UPDATE HISTOGRAM ON c0, c1;
explain analyze SELECT DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0;
explain analyze SELECT /*+ JOIN_SUFFIX(t2)*/ DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0;
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 DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0; | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=3.31..3.31 rows=1) (actual time=0.0748..0.0751 rows=1 loops=1) -> Temporary table with deduplication (cost=0.8..0.8 rows=1) (actual time=0.0739..0.0739 rows=1 loops=1) -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0497..0.0551 rows=1 loops=1) -> Table scan on t2 (cost=0.35 rows=1) (actual time=0.0289..0.0337 rows=1 loops=1) -> Filter: (t5.c1 = t2.c1) (cost=0.35 rows=1) (actual time=0.0187..0.0189 rows=1 loops=1) -> Single-row index lookup on t5 using PRIMARY (c0=t2.c0) (cost=0.35 rows=1) (actual time=0.0165..0.0166 rows=1 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) When utilizing the query hint JOIN_SUFFIX, this can be observed. mysql> explain analyze SELECT /*+ JOIN_SUFFIX(t2)*/ DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0; | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Group (no aggregates) (cost=0.8 rows=1) (actual time=0.0409..0.0411 rows=1 loops=1) -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0333..0.0374 rows=1 loops=1) -> Index scan on t5 using PRIMARY (cost=0.35 rows=1) (actual time=0.0206..0.0241 rows=1 loops=1) -> Filter: (t2.c1 = t5.c1) (cost=0.35 rows=1) (actual time=0.0111..0.0114 rows=1 loops=1) -> Single-row index lookup on t2 using PRIMARY (c0=t5.c0) (cost=0.35 rows=1) (actual time=0.01..0.0101 rows=1 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) How to repeat: CREATE TABLE t2(c0 INT PRIMARY KEY, c1 FLOAT ZEROFILL) ; CREATE TABLE t5 LIKE t2; INSERT INTO t2( c0, c1) VALUES(123, 456); INSERT INTO t5(c0, c1) VALUES(123, 456); ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1; ANALYZE TABLE t5 UPDATE HISTOGRAM ON c0, c1; explain analyze SELECT DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0; explain analyze SELECT /*+ JOIN_SUFFIX(t2)*/ DISTINCTROW t5.c0 FROM t2 NATURAL JOIN t5 GROUP BY t5.c0;