Description:
I have been investigating a query plan regression seen when migrating to 8.0
for 5.6. It is a pretty substantial regression that started taking 1500 seconds versus 2 seconds in 5.6.
Root cause was COND_FILTER_EQUALITY, COND_FILTER_INEQUALITY and COND_FILTER_BETWEEN were introduced as a part of patch https://github.com/mysql/mysql server/commit/063734feff5d54b323e3360fcebd983556cf2e3f. This is prone to cause regression in query plans on tables that do not contain a histogram. It would be
nice if there are no regressions for tables that do not have a histogram. This is
especially painful when upgrading the server to 8.0 with workloads running against 5.6 servers. It is unlikely that the workloads will have the distribution similar to COND_FILTER_EQUALITY, COND_FILTER_INEQUALITY and COND_FILTER_BETWEEN.
How to repeat:
Sample repro mysqltest script:
create table t1(a int primary key, b int, c int, key(b));
create table t2(a int primary key, b int, c int, unique key(b));
# Insert 10000 rows in t1 and 100 rows in t2
--disable_query_log
let $t1_count= 10000;
--echo # Insert $t1_count rows. Query log disabled.
while ($t1_count)
{
--eval INSERT INTO t1(a,b,c) VALUES ($t1_count, $t1_count/50, null);
dec $t1_count;
}
let $t2_count= 100;
--echo # Insert $t2_count rows. Query log disabled.
while ($t2_count)
{
--eval INSERT INTO t2(a,b,c) VALUES ($t2_count, $t2_count, $t2_count);
dec $t2_count;
}
--enable_query_log
ANALYZE TABLE t1;
ANALYZE TABLE t2;
EXPLAIN select count(*) from t1 join t2 using (b) where t1.c is null and t2.c <> 0;
select count(*) from t1 join t2 using (b) where t1.c is null and t2.c <> 0;
drop table t1;
drop table t2;
Output Plan:
EXPLAIN select count(*) from t1 join t2 using (b) where t1.c is null and t2.c <> 0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL b NULL NULL NULL 10000 10.00 Using where
1 SIMPLE t2 NULL eq_ref b b 5 test.t1.b 1 90.00 Using where
This query plan iterates over 10000 T1 rows because cost based estimation estimates that only 10% will satisfy "t1.c is null" predicate. However during actual execution, all the rows fit the predicate.
