diff options
author | Igor Babaev <igor@askmonty.org> | 2022-10-17 16:44:10 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2022-10-25 11:43:32 -0700 |
commit | 58cd0bd59ef011be54f162237f2ff017c3148e7b (patch) | |
tree | 7c6b5282b00ff9d9a509d85ff7d0ff996fa0d740 /mysql-test/main/subselect2.result | |
parent | f1bbc1cd19d0d81fee5433efcb570a8845172241 (diff) | |
download | mariadb-git-58cd0bd59ef011be54f162237f2ff017c3148e7b.tar.gz |
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 <sanja@mariadb.com>
Diffstat (limited to 'mysql-test/main/subselect2.result')
-rw-r--r-- | mysql-test/main/subselect2.result | 2 |
1 files changed, 1 insertions, 1 deletions
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index c54d635230f..55ac483157f 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -132,7 +132,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where -1 PRIMARY t3 ref|filter PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX FFOLDERID_IDX|CMFLDRPARNT_IDX 34|35 test.t3.PARENTID 1 (29%) Using where; Using rowid filter +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); |