Description:
After I create the database as follows:
CREATE DATABASE database0;
USE database0;
CREATE TABLE t0(c0 VARCHAR(500), c1 INT, c2 DOUBLE) ;
CREATE TABLE IF NOT EXISTS t1(c0 DOUBLE, c1 DOUBLE, c9 DOUBLE UNIQUE) ;
CREATE TABLE IF NOT EXISTS t2 LIKE t1;
SET @@cte_max_recursion_depth=1000000000;
SET @num_rows = 1000000; -- number of rows to generate
-- generate large number of data
INSERT IGNORE INTO t1(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT
n, -- c0
n, -- c1
n -- c9
FROM seq;
INSERT IGNORE INTO t2(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT
n, -- c0
n, -- c1
n -- c9
FROM seq;
I execute the SQL statement, it returns blank plan immediately(0 ms):
EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY t1.c1;
mysql> EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
-> FROM t2 NATURAL JOIN t1
-> WHERE (t2.c9)
-> BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
-> GROUP BY t1.c1;
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------+
| -> Zero rows (no matching row in const table) (cost=0..0 rows=0) (actual time=60e-6..60e-6 rows=0 loops=1)
|
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
then I add hint /*+ SET_VAR(range_alloc_block_size=10000000)*/, it generate execution plan and execute for a long time(650 ms):
EXPLAIN ANALYZE SELECT/*+ SET_VAR(range_alloc_block_size=10000000)*/ DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY t1.c1;
mysql> EXPLAIN ANALYZE SELECT/*+ SET_VAR(range_alloc_block_size=10000000)*/ DISTINCTROW t1.c1
-> FROM t2 NATURAL JOIN t1
-> WHERE (t2.c9)
-> BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
-> GROUP BY t1.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary> (cost=161390..161462 rows=5540) (actual time=650..650 rows=0 loops=1)
-> Temporary table with deduplication (cost=161390..161390 rows=5540) (actual time=650..650 rows=0 loops=1)
-> Nested loop inner join (cost=160836 rows=5540) (actual time=650..650 rows=0 loops=1)
-> Filter: ((t2.c9 between exists(select #2) and <cache>(-(1))) and (t2.c9 is not null)) (cost=100977 rows=110795) (actual time=650..650 rows=0 loops=1)
-> Table scan on t2 (cost=100977 rows=997255) (actual time=0.0227..541 rows=1e+6 loops=1)
-> Select #2 (subquery in condition; run only once)
-> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=60e-6..60e-6 rows=0 loops=1)
-> Filter: ((t1.c1 = t2.c1) and (t1.c0 = t2.c0)) (cost=0.44 rows=0.05) (never executed)
-> Single-row index lookup on t1 using c9 (c9=t2.c9) (cost=0.44 rows=1) (never executed)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 3 warnings (0.65 sec)
How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
CREATE TABLE t0(c0 VARCHAR(500), c1 INT, c2 DOUBLE) ;
CREATE TABLE IF NOT EXISTS t1(c0 DOUBLE, c1 DOUBLE, c9 DOUBLE UNIQUE) ;
CREATE TABLE IF NOT EXISTS t2 LIKE t1;
SET @@cte_max_recursion_depth=1000000000;
SET @num_rows = 1000000; -- number of rows to generate
-- generate large number of data
INSERT IGNORE INTO t1(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT
n, -- c0
n, -- c1
n -- c9
FROM seq;
INSERT IGNORE INTO t2(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT
n, -- c0
n, -- c1
n -- c9
FROM seq;
EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY t1.c1;
EXPLAIN ANALYZE SELECT/*+ SET_VAR(range_alloc_block_size=10000000)*/ DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY t1.c1;