From 58cd0bd59ef011be54f162237f2ff017c3148e7b Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 17 Oct 2022 16:44:10 -0700 Subject: MDEV-28846 Poor performance when rowid filter contains no elements When a range rowid filter was used with an index ref access the cost of accessing the index entries for the records rejected by the filter was not taken into account. For a ref access by an index with big average number of records per key this led to poor execution plans if selectivity of the used filter was high. The patch resolves this problem. It also introduces a minor optimization that skips look-ups into a filter that turns out to be empty. With this patch the output of ANALYZE stmt reports the number of look-ups into used rowid filters. The patch also back-ports from 10.5 the code that properly sets the field TABLE::file::table for opened temporary tables. The test cases that were supposed to use rowid filters have been adjusted in order to use similar execution plans after this fix. Approved by Oleksandr Byelkin --- mysql-test/include/rowid_filter_debug_kill.inc | 9 +++------ 1 file changed, 3 insertions(+), 6 deletions(-) (limited to 'mysql-test/include') diff --git a/mysql-test/include/rowid_filter_debug_kill.inc b/mysql-test/include/rowid_filter_debug_kill.inc index 6a8c5d3f70d..c701d206297 100644 --- a/mysql-test/include/rowid_filter_debug_kill.inc +++ b/mysql-test/include/rowid_filter_debug_kill.inc @@ -9,9 +9,6 @@ create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1(a int); -insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; - # 100 rows create table t2(a int); insert into t2 select A.a + B.a* 10 from t0 A, t0 B; @@ -30,10 +27,10 @@ where table_schema=database() and table_name='t3'; insert into t3 select A.a, - A.a, + B.a, 'filler-data-filler-data' from - t0 A, t1 B; + t2 A, t2 B; analyze table t2,t3; @@ -63,6 +60,6 @@ disconnect con1; reap; set debug_sync='RESET'; -drop table t0,t1,t2,t3; +drop table t0,t2,t3; --source include/wait_until_count_sessions.inc -- cgit v1.2.1