MDEV-28846 Poor performance when rowid filter contains no elementsbb-10.5-MDEV-28846

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 test cases that were supposed to use rowid filters have been adjusted
in order to use similar execution plans after this fix.

This patch prepared for 10.5 differs from the patch resolving the problem
for 10.4 because he code that calculates the cost of index only access has
been changed for 10.5 making usage of rowid filters more favorable.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

21 files changed, 1791 insertions, 173 deletions

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
 
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index f2893f27e8e..0ac104ab9d3 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -853,7 +853,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (flat, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1053,7 +1053,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (incremental, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1312,7 +1312,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1509,7 +1509,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1706,7 +1706,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1903,7 +1903,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2104,7 +2104,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (flat, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2208,7 +2208,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (incremental, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2312,7 +2312,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2416,7 +2416,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2520,7 +2520,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2624,7 +2624,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result
index f7d0242244d..41b47bfa868 100644
--- a/mysql-test/main/join_nested_jcl6.result
+++ b/mysql-test/main/join_nested_jcl6.result
@@ -2033,7 +2033,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND
 (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	
-1	SIMPLE	t7	ref|filter	PRIMARY,b_i	b_i|PRIMARY	5|4	test.t5.b	2 (29%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	t7	range	PRIMARY,b_i	PRIMARY	4	NULL	2	Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
 1	SIMPLE	t6	range|filter	PRIMARY,b_i	PRIMARY|b_i	4|5	NULL	3 (86%)	Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join); Using rowid filter
 1	SIMPLE	t8	ref	b_i	b_i	5	test.t5.b	2	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
diff --git a/mysql-test/main/partition_pruning.result b/mysql-test/main/partition_pruning.result
index 519bf590b9b..ec0cb144a51 100644
--- a/mysql-test/main/partition_pruning.result
+++ b/mysql-test/main/partition_pruning.result
@@ -2677,7 +2677,7 @@ select * from t1 X, t1 Y
 where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	X	p1,p2	range	a,b	a	4	NULL	4	Using where
-1	SIMPLE	Y	p2,p3	ref|filter	a,b	b|a	4|4	test.X.b	2 (50%)	Using where; Using rowid filter
+1	SIMPLE	Y	p2,p3	ref	a,b	b	4	test.X.b	2	Using where
 explain partitions
 select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 81098118abd..145464896b3 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -281,7 +281,7 @@ INSERT INTO t1 VALUES
 (33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
 EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref|filter	a,b	b|a	5|5	const	15 (5%)	Using where; Using rowid filter
+1	SIMPLE	t1	range|filter	a,b	a|b	5|5	NULL	2 (41%)	Using index condition; Using where; Using rowid filter
 SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
 a	b
 DROP TABLE t1;
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 63b43204861..f7a340b57c5 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -128,6 +128,7 @@ ANALYZE
         "rows": 702,
         "selectivity_pct": 11.69025812,
         "r_rows": 605,
+        "r_lookups": 510,
         "r_selectivity_pct": 11.76470588,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -438,6 +439,7 @@ ANALYZE
         "rows": 69,
         "selectivity_pct": 4.6,
         "r_rows": 71,
+        "r_lookups": 96,
         "r_selectivity_pct": 10.41666667,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -692,6 +694,7 @@ ANALYZE
         "rows": 702,
         "selectivity_pct": 11.69025812,
         "r_rows": 605,
+        "r_lookups": 510,
         "r_selectivity_pct": 11.76470588,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -722,6 +725,7 @@ ANALYZE
         "rows": 139,
         "selectivity_pct": 9.266666667,
         "r_rows": 144,
+        "r_lookups": 59,
         "r_selectivity_pct": 25.42372881,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -998,6 +1002,7 @@ ANALYZE
         "rows": 509,
         "selectivity_pct": 8.476269775,
         "r_rows": 510,
+        "r_lookups": 476,
         "r_selectivity_pct": 7.773109244,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -2045,7 +2050,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+2	SUBQUERY	t2	ref	i1,i2	i1	5	const	1	100.00	Using index condition; Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0
 DROP TABLE t1,t2;
@@ -2054,7 +2059,7 @@ DROP TABLE t1,t2;
 #             that uses in expensive subquery
 #
 CREATE TABLE t1 (
-pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1)
+pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(a1), KEY(b1)
 ) ENGINE=MyISAM;
 INSERT INTO t1 VALUES
 (10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'),
@@ -2073,21 +2078,31 @@ INSERT INTO t1 VALUES
 (101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'),
 (107,8,'z'),(108,3,'k'),(109,65,NULL);
 CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM;
-INSERT INTO t2 VALUES (1,1,'x');
+INSERT INTO t2 VALUES (1,1,'i');
 INSERT INTO t2 SELECT * FROM t1;
-SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+INSERT INTO t1 SELECT pk1+200, a1, b1 FROM t1;
+INSERT INTO t1 SELECT pk1+400, a1, b1 FROM t1;
+ANALYZE TABLE t1,t2 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 pk1	a1	b1	pk2	a2	b2
-65	2	a	109	65	NULL
-EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+17	1	f	16	1	j
+37	3	g	36	3	a
+105	8	i	104	8	e
+EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	101	100.00	Using where
-1	PRIMARY	t1	eq_ref|filter	PRIMARY,b1	PRIMARY|b1	4|4	test.t2.a2	1 (87%)	87.00	Using where; Using rowid filter
+1	PRIMARY	t1	ref|filter	a1,b1	a1|b1	5|4	test.t2.a2	36 (29%)	28.75	Using where; Using rowid filter
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index condition
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2`
-EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2
+EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 EXPLAIN
 {
@@ -2098,27 +2113,27 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 101,
       "filtered": 100,
-      "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null"
+      "attached_condition": "t2.a2 is not null"
     },
     "table": {
       "table_name": "t1",
-      "access_type": "eq_ref",
-      "possible_keys": ["PRIMARY", "b1"],
-      "key": "PRIMARY",
-      "key_length": "4",
-      "used_key_parts": ["pk1"],
+      "access_type": "ref",
+      "possible_keys": ["a1", "b1"],
+      "key": "a1",
+      "key_length": "5",
+      "used_key_parts": ["a1"],
       "ref": ["test.t2.a2"],
       "rowid_filter": {
         "range": {
           "key": "b1",
           "used_key_parts": ["b1"]
         },
-        "rows": 87,
-        "selectivity_pct": 87
+        "rows": 115,
+        "selectivity_pct": 28.75
       },
-      "rows": 1,
-      "filtered": 87,
-      "attached_condition": "t1.b1 <= (subquery#2)"
+      "rows": 36,
+      "filtered": 28.75,
+      "attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2"
     },
     "subqueries": [
       {
@@ -2185,13 +2200,446 @@ set @save_optimizer_switch= @@optimizer_switch;
 SET @@optimizer_switch="index_merge_sort_union=OFF";
 CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b));
 INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+ANALYZE table t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
 explain
 SELECT * FROM t1 WHERE a > 0 AND b=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref|filter	a,b	b|a	5|5	const	2 (14%)	Using where; Using rowid filter
+1	SIMPLE	t1	range|filter	a,b	a|b	5|5	NULL	77 (34%)	Using index condition; Using where; Using rowid filter
 SELECT * FROM t1 WHERE a > 0 AND b=0;
 a	b
 1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
 drop table t1;
 SET @@optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-28846: Poor performance when rowid filter contains no elements
+#
+create table t1 (
+pk int primary key auto_increment,
+nm varchar(32),
+fl1 tinyint default 0,
+fl2 tinyint default 0,
+index idx1(nm, fl1),
+index idx2(fl2)
+) engine=myisam;
+create table name (
+pk int primary key auto_increment,
+nm bigint
+) engine=myisam;
+create table flag2 (
+pk int primary key auto_increment,
+fl2 tinyint
+) engine=myisam;
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select '500%' as a;
+a
+500%
+set optimizer_switch='rowid_filter=on';
+explain
+select * from t1  where nm like '500%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	35	NULL	1	Using index condition; Using where
+analyze format=json
+select * from t1  where nm like '500%' AND fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "35",
+      "used_key_parts": ["nm"],
+      "r_loops": 1,
+      "rows": 1,
+      "r_rows": 1,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 49.20000076,
+      "r_filtered": 100,
+      "index_condition": "t1.nm like '500%'",
+      "attached_condition": "t1.fl2 = 0"
+    }
+  }
+}
+select * from t1  where nm like '500%' AND fl2 = 0;
+pk	nm	fl1	fl2
+517	500	0	0
+truncate table name;
+truncate table flag2;
+truncate table t1;
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+set optimizer_switch='rowid_filter=off';
+explain
+select * from t1  where nm like '500%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	35	NULL	1	Using index condition; Using where
+analyze format=json
+select * from t1  where nm like '500%' AND fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "35",
+      "used_key_parts": ["nm"],
+      "r_loops": 1,
+      "rows": 1,
+      "r_rows": 1,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 49.20000076,
+      "r_filtered": 100,
+      "index_condition": "t1.nm like '500%'",
+      "attached_condition": "t1.fl2 = 0"
+    }
+  }
+}
+select * from t1  where nm like '500%' AND fl2 = 0;
+pk	nm	fl1	fl2
+517	500	0	0
+truncate table name;
+truncate table flag2;
+truncate table t1;
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 10 from seq_1_to_1000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select '607%' as a;
+a
+607%
+set optimizer_switch='rowid_filter=on';
+explain
+select * from t1  where nm like '607%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	35	NULL	1	Using index condition; Using where
+select * from t1  where nm like '607%' AND fl2 = 0;
+pk	nm	fl1	fl2
+721	607	0	0
+truncate table name;
+truncate table flag2;
+truncate table t1;
+insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
+insert into flag2(fl2) select seq mod 100 from seq_1_to_10000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select '75%' as a;
+a
+75%
+set optimizer_switch='rowid_filter=on';
+explain
+select * from t1  where nm like '75%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref|filter	idx1,idx2	idx2|idx1	2|35	const	55 (1%)	Using where; Using rowid filter
+analyze format=json
+select * from t1  where nm like '75%' AND fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "2",
+      "used_key_parts": ["fl2"],
+      "ref": ["const"],
+      "rowid_filter": {
+        "range": {
+          "key": "idx1",
+          "used_key_parts": ["nm"]
+        },
+        "rows": 115,
+        "selectivity_pct": 1.15,
+        "r_rows": 111,
+        "r_lookups": 100,
+        "r_selectivity_pct": 2,
+        "r_buffer_size": "REPLACED",
+        "r_filling_time_ms": "REPLACED"
+      },
+      "r_loops": 1,
+      "rows": 55,
+      "r_rows": 2,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 1.149999976,
+      "r_filtered": 100,
+      "attached_condition": "t1.nm like '75%'"
+    }
+  }
+}
+select * from t1  where nm like '75%' AND fl2 = 0;
+pk	nm	fl1	fl2
+4543	7503	0	0
+7373	7518	0	0
+drop table name, flag2;
+drop table t1;
+create table t1 (
+pk int primary key auto_increment,
+nm char(255),
+fl1 tinyint default 0,
+fl2 int default 0,
+index idx1(nm, fl1),
+index idx2(fl2)
+) engine=myisam;
+create table name (
+pk int primary key auto_increment,
+nm bigint
+) engine=myisam;
+create table flag2 (
+pk int primary key auto_increment,
+fl2 int
+) engine=myisam;
+insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
+insert into flag2(fl2) select seq mod 10 from seq_1_to_10000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select * from t1
+where
+(
+nm like '3400%' or nm like '3402%' or nm like '3403%' or
+nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+nm like '3409%' or
+nm like '3411%' or nm like '3412%' or nm like '3413%' or
+nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+nm like '3418%' or nm like '3419%' or
+nm like '3421%' or nm like '3422%' or nm like '3423%' or
+nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+nm like '3428%' or nm like '3429%' or
+nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+nm like '3439%' or
+nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+nm like '3448%'
+) and fl2 = 0;
+pk	nm	fl1	fl2
+analyze format=json select * from t1
+where
+(
+nm like '3400%' or nm like '3402%' or nm like '3403%' or
+nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+nm like '3409%' or
+nm like '3411%' or nm like '3412%' or nm like '3413%' or
+nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+nm like '3418%' or nm like '3419%' or
+nm like '3421%' or nm like '3422%' or nm like '3423%' or
+nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+nm like '3428%' or nm like '3429%' or
+nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+nm like '3439%' or
+nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+nm like '3448%'
+) and fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "5",
+      "used_key_parts": ["fl2"],
+      "ref": ["const"],
+      "rowid_filter": {
+        "range": {
+          "key": "idx1",
+          "used_key_parts": ["nm"]
+        },
+        "rows": 44,
+        "selectivity_pct": 0.44,
+        "r_rows": 44,
+        "r_lookups": 1000,
+        "r_selectivity_pct": 0,
+        "r_buffer_size": "REPLACED",
+        "r_filling_time_ms": "REPLACED"
+      },
+      "r_loops": 1,
+      "rows": 863,
+      "r_rows": 0,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 0.439999998,
+      "r_filtered": 100,
+      "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
+    }
+  }
+}
+create table t0 select * from t1  where nm like '34%';
+delete from t1 using t1,t0 where t1.nm=t0.nm;
+analyze format=json select * from t1
+where
+(
+nm like '3400%' or nm like '3402%' or nm like '3403%' or
+nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+nm like '3409%' or
+nm like '3411%' or nm like '3412%' or nm like '3413%' or
+nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+nm like '3418%' or nm like '3419%' or
+nm like '3421%' or nm like '3422%' or nm like '3423%' or
+nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+nm like '3428%' or nm like '3429%' or
+nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+nm like '3439%' or
+nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+nm like '3448%'
+) and fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "5",
+      "used_key_parts": ["fl2"],
+      "ref": ["const"],
+      "rowid_filter": {
+        "range": {
+          "key": "idx1",
+          "used_key_parts": ["nm"]
+        },
+        "rows": 44,
+        "selectivity_pct": 0.44,
+        "r_rows": 0,
+        "r_lookups": 0,
+        "r_selectivity_pct": 0,
+        "r_buffer_size": "REPLACED",
+        "r_filling_time_ms": "REPLACED"
+      },
+      "r_loops": 1,
+      "rows": 853,
+      "r_rows": 0,
+      "filtered": 0.439999998,
+      "r_filtered": 100,
+      "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
+    }
+  }
+}
+drop table t0;
+set optimizer_switch='rowid_filter=default';
+drop table name, flag2;
+drop table t1;
 set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index 163b71b6153..df0efbd8a17 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -320,7 +320,7 @@ DROP TABLE t1,t2;
 --echo #
 
 CREATE TABLE t1 (
-  pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1)
+  pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(a1), KEY(b1)
 ) ENGINE=MyISAM;
 INSERT INTO t1 VALUES
 (10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'),
@@ -340,13 +340,18 @@ INSERT INTO t1 VALUES
 (107,8,'z'),(108,3,'k'),(109,65,NULL);
 
 CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM;
-INSERT INTO t2 VALUES (1,1,'x');
+INSERT INTO t2 VALUES (1,1,'i');
 INSERT INTO t2 SELECT * FROM t1;
 
 let $q=
-SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
   WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 
+INSERT INTO t1 SELECT pk1+200, a1, b1 FROM t1;
+INSERT INTO t1 SELECT pk1+400, a1, b1 FROM t1;
+
+ANALYZE TABLE t1,t2 PERSISTENT FOR ALL;
+
 eval $q;
 eval EXPLAIN EXTENDED $q;
 eval EXPLAIN FORMAT=JSON $q;
@@ -399,6 +404,15 @@ set @save_optimizer_switch= @@optimizer_switch;
 SET @@optimizer_switch="index_merge_sort_union=OFF";
 CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b));
 INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+
+ANALYZE table t1 PERSISTENT FOR ALL;
+
 explain
 SELECT * FROM t1 WHERE a > 0 AND b=0;
 SELECT * FROM t1 WHERE a > 0 AND b=0;
@@ -406,4 +420,194 @@ drop table t1;
 SET @@optimizer_switch=@save_optimizer_switch;
 
 
+--echo #
+--echo # MDEV-28846: Poor performance when rowid filter contains no elements
+--echo #
+
+--source include/have_sequence.inc
+
+create table t1 (
+  pk int primary key auto_increment,
+  nm varchar(32),
+  fl1 tinyint default 0,
+  fl2 tinyint default 0,
+  index idx1(nm, fl1),
+  index idx2(fl2)
+) engine=myisam;
+
+create table name (
+  pk int primary key auto_increment,
+  nm bigint
+) engine=myisam;
+
+create table flag2 (
+  pk int primary key auto_increment,
+  fl2 tinyint
+) engine=myisam;
+
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
+
+insert into t1(nm,fl2)
+  select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+
+analyze table t1 persistent for all;
+
+let $a=
+`select concat((select nm from t1 where fl2=0 order by RAND(13) limit 1),'%')`;
+eval select '$a' as a;
+
+set optimizer_switch='rowid_filter=on';
+eval
+explain
+select * from t1  where nm like '$a' AND fl2 = 0;
+--source include/analyze-format.inc
+eval
+analyze format=json
+select * from t1  where nm like '$a' AND fl2 = 0;
+eval
+select * from t1  where nm like '$a' AND fl2 = 0;
+
+truncate table name;
+truncate table flag2;
+truncate table t1;
+
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
+
+insert into t1(nm,fl2)
+  select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+
+analyze table t1 persistent for all;
+
+set optimizer_switch='rowid_filter=off';
+eval
+explain
+select * from t1  where nm like '$a' AND fl2 = 0;
+--source include/analyze-format.inc
+eval
+analyze format=json
+select * from t1  where nm like '$a' AND fl2 = 0;
+eval
+select * from t1  where nm like '$a' AND fl2 = 0;
+
+truncate table name;
+truncate table flag2;
+truncate table t1;
+
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 10 from seq_1_to_1000 order by rand(19);
+
+insert into t1(nm,fl2)
+  select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+
+analyze table t1 persistent for all;
+
+let $a=
+`select concat((select nm from t1 where fl2=0 order by RAND(13) limit 1),'%')`;
+eval select '$a' as a;
+
+set optimizer_switch='rowid_filter=on';
+eval
+explain
+select * from t1  where nm like '$a' AND fl2 = 0;
+eval
+select * from t1  where nm like '$a' AND fl2 = 0;
+
+truncate table name;
+truncate table flag2;
+truncate table t1;
+
+insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
+insert into flag2(fl2) select seq mod 100 from seq_1_to_10000 order by rand(19);
+
+insert into t1(nm,fl2)
+  select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+
+analyze table t1 persistent for all;
+
+let $a=
+`select concat(left((select nm from t1 where fl2=0 order by RAND(13) limit 1),2),'%')`;
+eval select '$a' as a;
+
+set optimizer_switch='rowid_filter=on';
+eval
+explain
+select * from t1  where nm like '$a' AND fl2 = 0;
+--source include/analyze-format.inc
+eval
+analyze format=json
+select * from t1  where nm like '$a' AND fl2 = 0;
+eval
+select * from t1  where nm like '$a' AND fl2 = 0;
+
+drop table name, flag2;
+drop table t1;
+
+# This test shows that if the container is empty there are no lookups into it
+
+create table t1 (
+  pk int primary key auto_increment,
+  nm char(255),
+  fl1 tinyint default 0,
+  fl2 int default 0,
+  index idx1(nm, fl1),
+  index idx2(fl2)
+) engine=myisam;
+
+create table name (
+  pk int primary key auto_increment,
+  nm bigint
+) engine=myisam;
+
+create table flag2 (
+  pk int primary key auto_increment,
+  fl2 int
+) engine=myisam;
+
+insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
+insert into flag2(fl2) select seq mod 10 from seq_1_to_10000 order by rand(19);
+
+insert into t1(nm,fl2)
+  select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+
+analyze table t1 persistent for all;
+
+let $q=
+select * from t1
+where
+(
+  nm like '3400%' or nm like '3402%' or nm like '3403%' or
+  nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+  nm like '3409%' or
+  nm like '3411%' or nm like '3412%' or nm like '3413%' or
+  nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+  nm like '3418%' or nm like '3419%' or
+  nm like '3421%' or nm like '3422%' or nm like '3423%' or
+  nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+  nm like '3428%' or nm like '3429%' or
+  nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+  nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+  nm like '3439%' or
+  nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+  nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+  nm like '3448%'
+) and fl2 = 0;
+
+eval $q;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+
+create table t0 select * from t1  where nm like '34%';
+delete from t1 using t1,t0 where t1.nm=t0.nm;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+
+drop table t0;
+
+set optimizer_switch='rowid_filter=default';
+
+drop table name, flag2;
+drop table t1;
+
 set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 658cede61a1..f52d2c9f9c1 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -129,6 +129,7 @@ ANALYZE
         "rows": 605,
         "selectivity_pct": 10.07493755,
         "r_rows": 605,
+        "r_lookups": 510,
         "r_selectivity_pct": 11.76470588,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -669,6 +670,7 @@ ANALYZE
         "rows": 605,
         "selectivity_pct": 10.07493755,
         "r_rows": 605,
+        "r_lookups": 510,
         "r_selectivity_pct": 11.76470588,
         "r_buffer_size": "REPLACED",
         "r_filling_time_ms": "REPLACED"
@@ -1994,7 +1996,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+2	SUBQUERY	t2	ref	i1,i2	i1	5	const	1	100.00	Using index condition; Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0
 DROP TABLE t1,t2;
@@ -2003,7 +2005,7 @@ DROP TABLE t1,t2;
 #             that uses in expensive subquery
 #
 CREATE TABLE t1 (
-pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1)
+pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(a1), KEY(b1)
 ) ENGINE=MyISAM;
 INSERT INTO t1 VALUES
 (10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'),
@@ -2022,21 +2024,31 @@ INSERT INTO t1 VALUES
 (101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'),
 (107,8,'z'),(108,3,'k'),(109,65,NULL);
 CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM;
-INSERT INTO t2 VALUES (1,1,'x');
+INSERT INTO t2 VALUES (1,1,'i');
 INSERT INTO t2 SELECT * FROM t1;
-SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+INSERT INTO t1 SELECT pk1+200, a1, b1 FROM t1;
+INSERT INTO t1 SELECT pk1+400, a1, b1 FROM t1;
+ANALYZE TABLE t1,t2 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 pk1	a1	b1	pk2	a2	b2
-65	2	a	109	65	NULL
-EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+17	1	f	16	1	j
+37	3	g	36	3	a
+105	8	i	104	8	e
+EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	101	100.00	Using where
-1	PRIMARY	t1	eq_ref|filter	PRIMARY,b1	PRIMARY|b1	4|4	test.t2.a2	1 (87%)	87.00	Using where; Using rowid filter
+1	PRIMARY	t1	ref|filter	a1,b1	a1|b1	5|4	test.t2.a2	36 (29%)	28.75	Using where; Using rowid filter
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index condition
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2`
-EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2
+EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 EXPLAIN
 {
@@ -2047,27 +2059,27 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 101,
       "filtered": 100,
-      "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null"
+      "attached_condition": "t2.a2 is not null"
     },
     "table": {
       "table_name": "t1",
-      "access_type": "eq_ref",
-      "possible_keys": ["PRIMARY", "b1"],
-      "key": "PRIMARY",
-      "key_length": "4",
-      "used_key_parts": ["pk1"],
+      "access_type": "ref",
+      "possible_keys": ["a1", "b1"],
+      "key": "a1",
+      "key_length": "5",
+      "used_key_parts": ["a1"],
       "ref": ["test.t2.a2"],
       "rowid_filter": {
         "range": {
           "key": "b1",
           "used_key_parts": ["b1"]
         },
-        "rows": 87,
-        "selectivity_pct": 87
+        "rows": 115,
+        "selectivity_pct": 28.75
       },
-      "rows": 1,
-      "filtered": 87,
-      "attached_condition": "t1.b1 <= (subquery#2)"
+      "rows": 36,
+      "filtered": 28.75,
+      "attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2"
     },
     "subqueries": [
       {
@@ -2134,15 +2146,448 @@ set @save_optimizer_switch= @@optimizer_switch;
 SET @@optimizer_switch="index_merge_sort_union=OFF";
 CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b));
 INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+ANALYZE table t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
 explain
 SELECT * FROM t1 WHERE a > 0 AND b=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref|filter	a,b	b|a	5|5	const	2 (14%)	Using where; Using rowid filter
+1	SIMPLE	t1	range|filter	a,b	a|b	5|5	NULL	64 (29%)	Using index condition; Using where; Using rowid filter
 SELECT * FROM t1 WHERE a > 0 AND b=0;
 a	b
 1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
+1	0
 drop table t1;
 SET @@optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-28846: Poor performance when rowid filter contains no elements
+#
+create table t1 (
+pk int primary key auto_increment,
+nm varchar(32),
+fl1 tinyint default 0,
+fl2 tinyint default 0,
+index idx1(nm, fl1),
+index idx2(fl2)
+) engine=myisam;
+create table name (
+pk int primary key auto_increment,
+nm bigint
+) engine=myisam;
+create table flag2 (
+pk int primary key auto_increment,
+fl2 tinyint
+) engine=myisam;
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select '500%' as a;
+a
+500%
+set optimizer_switch='rowid_filter=on';
+explain
+select * from t1  where nm like '500%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	35	NULL	1	Using index condition; Using where
+analyze format=json
+select * from t1  where nm like '500%' AND fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "35",
+      "used_key_parts": ["nm"],
+      "r_loops": 1,
+      "rows": 1,
+      "r_rows": 1,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 49.20000076,
+      "r_filtered": 100,
+      "index_condition": "t1.nm like '500%'",
+      "attached_condition": "t1.fl2 = 0"
+    }
+  }
+}
+select * from t1  where nm like '500%' AND fl2 = 0;
+pk	nm	fl1	fl2
+517	500	0	0
+truncate table name;
+truncate table flag2;
+truncate table t1;
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+set optimizer_switch='rowid_filter=off';
+explain
+select * from t1  where nm like '500%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	35	NULL	1	Using index condition; Using where
+analyze format=json
+select * from t1  where nm like '500%' AND fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "35",
+      "used_key_parts": ["nm"],
+      "r_loops": 1,
+      "rows": 1,
+      "r_rows": 1,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 49.20000076,
+      "r_filtered": 100,
+      "index_condition": "t1.nm like '500%'",
+      "attached_condition": "t1.fl2 = 0"
+    }
+  }
+}
+select * from t1  where nm like '500%' AND fl2 = 0;
+pk	nm	fl1	fl2
+517	500	0	0
+truncate table name;
+truncate table flag2;
+truncate table t1;
+insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
+insert into flag2(fl2) select seq mod 10 from seq_1_to_1000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select '607%' as a;
+a
+607%
+set optimizer_switch='rowid_filter=on';
+explain
+select * from t1  where nm like '607%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	35	NULL	1	Using index condition; Using where
+select * from t1  where nm like '607%' AND fl2 = 0;
+pk	nm	fl1	fl2
+721	607	0	0
+truncate table name;
+truncate table flag2;
+truncate table t1;
+insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
+insert into flag2(fl2) select seq mod 100 from seq_1_to_10000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select '75%' as a;
+a
+75%
+set optimizer_switch='rowid_filter=on';
+explain
+select * from t1  where nm like '75%' AND fl2 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref|filter	idx1,idx2	idx2|idx1	2|35	const	55 (1%)	Using where; Using rowid filter
+analyze format=json
+select * from t1  where nm like '75%' AND fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "2",
+      "used_key_parts": ["fl2"],
+      "ref": ["const"],
+      "rowid_filter": {
+        "range": {
+          "key": "idx1",
+          "used_key_parts": ["nm"]
+        },
+        "rows": 115,
+        "selectivity_pct": 1.15,
+        "r_rows": 111,
+        "r_lookups": 100,
+        "r_selectivity_pct": 2,
+        "r_buffer_size": "REPLACED",
+        "r_filling_time_ms": "REPLACED"
+      },
+      "r_loops": 1,
+      "rows": 55,
+      "r_rows": 2,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 1.149999976,
+      "r_filtered": 100,
+      "attached_condition": "t1.nm like '75%'"
+    }
+  }
+}
+select * from t1  where nm like '75%' AND fl2 = 0;
+pk	nm	fl1	fl2
+4543	7503	0	0
+7373	7518	0	0
+drop table name, flag2;
+drop table t1;
+create table t1 (
+pk int primary key auto_increment,
+nm char(255),
+fl1 tinyint default 0,
+fl2 int default 0,
+index idx1(nm, fl1),
+index idx2(fl2)
+) engine=myisam;
+create table name (
+pk int primary key auto_increment,
+nm bigint
+) engine=myisam;
+create table flag2 (
+pk int primary key auto_increment,
+fl2 int
+) engine=myisam;
+insert into name(nm) select seq from seq_1_to_10000 order by rand(17);
+insert into flag2(fl2) select seq mod 10 from seq_1_to_10000 order by rand(19);
+insert into t1(nm,fl2)
+select nm, fl2 from name, flag2 where name.pk = flag2.pk;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+select * from t1
+where
+(
+nm like '3400%' or nm like '3402%' or nm like '3403%' or
+nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+nm like '3409%' or
+nm like '3411%' or nm like '3412%' or nm like '3413%' or
+nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+nm like '3418%' or nm like '3419%' or
+nm like '3421%' or nm like '3422%' or nm like '3423%' or
+nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+nm like '3428%' or nm like '3429%' or
+nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+nm like '3439%' or
+nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+nm like '3448%'
+) and fl2 = 0;
+pk	nm	fl1	fl2
+analyze format=json select * from t1
+where
+(
+nm like '3400%' or nm like '3402%' or nm like '3403%' or
+nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+nm like '3409%' or
+nm like '3411%' or nm like '3412%' or nm like '3413%' or
+nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+nm like '3418%' or nm like '3419%' or
+nm like '3421%' or nm like '3422%' or nm like '3423%' or
+nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+nm like '3428%' or nm like '3429%' or
+nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+nm like '3439%' or
+nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+nm like '3448%'
+) and fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "5",
+      "used_key_parts": ["fl2"],
+      "ref": ["const"],
+      "rowid_filter": {
+        "range": {
+          "key": "idx1",
+          "used_key_parts": ["nm"]
+        },
+        "rows": 44,
+        "selectivity_pct": 0.44,
+        "r_rows": 44,
+        "r_lookups": 1000,
+        "r_selectivity_pct": 0,
+        "r_buffer_size": "REPLACED",
+        "r_filling_time_ms": "REPLACED"
+      },
+      "r_loops": 1,
+      "rows": 863,
+      "r_rows": 0,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 0.439999998,
+      "r_filtered": 100,
+      "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
+    }
+  }
+}
+create table t0 select * from t1  where nm like '34%';
+delete from t1 using t1,t0 where t1.nm=t0.nm;
+analyze format=json select * from t1
+where
+(
+nm like '3400%' or nm like '3402%' or nm like '3403%' or
+nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or
+nm like '3409%' or
+nm like '3411%' or nm like '3412%' or nm like '3413%' or
+nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or
+nm like '3418%' or nm like '3419%' or
+nm like '3421%' or nm like '3422%' or nm like '3423%' or
+nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or
+nm like '3428%' or nm like '3429%' or
+nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or
+nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or
+nm like '3439%' or
+nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or
+nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or
+nm like '3448%'
+) and fl2 = 0;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "5",
+      "used_key_parts": ["fl2"],
+      "ref": ["const"],
+      "rowid_filter": {
+        "range": {
+          "key": "idx1",
+          "used_key_parts": ["nm"]
+        },
+        "rows": 44,
+        "selectivity_pct": 0.44,
+        "r_rows": 0,
+        "r_lookups": 0,
+        "r_selectivity_pct": 0,
+        "r_buffer_size": "REPLACED",
+        "r_filling_time_ms": "REPLACED"
+      },
+      "r_loops": 1,
+      "rows": 853,
+      "r_rows": 0,
+      "filtered": 0.439999998,
+      "r_filtered": 100,
+      "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
+    }
+  }
+}
+drop table t0;
+set optimizer_switch='rowid_filter=default';
+drop table name, flag2;
+drop table t1;
 set @@use_stat_tables=@save_use_stat_tables;
 #
 # MDEV-18755: possible RORI-plan and possible plan with range filter
@@ -2167,6 +2612,10 @@ insert into t1 values
 (81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null),
 (85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160),
 (89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
 ( select * from t1
 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a')))
 union
@@ -2277,46 +2726,44 @@ drop table t1, t2;
 #
 create table t1 (a int, b int, key (b), key (a)) engine=innodb;
 insert into t1
-select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
+select (rand(1)*1000)/10, (rand(1001)*1000)/20 from seq_1_to_1000;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 set @save_optimizer_switch= @@optimizer_switch;
 set optimizer_switch='rowid_filter=off';
-select count(*) from t1 where a in (22,83,11) and b=2;
+select count(*) from t1 where a between 21 and 30 and b=2;
 count(*)
-6
-explain extended select count(*) from t1 where a in (22,83,11) and b=2;
+5
+explain extended select count(*) from t1 where a between 21 and 30 and b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	range	b,a	a	5	NULL	33	5.90	Using index condition; Using where
+1	SIMPLE	t1	ref	b,a	b	5	const	24	9.60	Using where
 Warnings:
-Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11)
-select * from t1 where a in (22,83,11) and b=2;
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` between 21 and 30
+select * from t1 where a between 21 and 30 and b=2;
 a	b
-11	2
-11	2
-11	2
+30	2
+21	2
 22	2
-83	2
-83	2
+26	2
+25	2
 set optimizer_switch='rowid_filter=on';
-select count(*) from t1 where a in (22,83,11) and b=2;
+select count(*) from t1 where a between 21 and 30 and b=2;
 count(*)
-6
-explain extended select count(*) from t1 where a in (22,83,11) and b=2;
+5
+explain extended select count(*) from t1 where a between 21 and 30 and b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	59 (3%)	3.30	Using where; Using rowid filter
+1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	24 (10%)	9.60	Using where; Using rowid filter
 Warnings:
-Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11)
-select * from t1 where a in (22,83,11) and b=2;
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` between 21 and 30
+select * from t1 where a between 21 and 30 and b=2;
 a	b
-11	2
-11	2
-83	2
-11	2
-83	2
+30	2
+21	2
 22	2
+26	2
+25	2
 drop table t1;
 set optimizer_switch=@save_optimizer_switch;
 SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT;
@@ -2471,7 +2918,7 @@ set global innodb_stats_persistent= @stats.save;
 #
 CREATE TABLE t1 (
 id int(11) unsigned NOT NULL AUTO_INCREMENT,
-domain varchar(255) NOT NULL,
+domain varchar(32) NOT NULL,
 registrant_name varchar(255) DEFAULT NULL,
 registrant_organization varchar(255) DEFAULT NULL,
 registrant_street1 varchar(255) DEFAULT NULL,
@@ -2562,21 +3009,216 @@ null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937',
 'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null,
 null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM',
 '492214307580', '', '2017-01-30 10:08:29');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28');
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+SELECT
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp
+FROM t1;
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+SELECT
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp
+FROM t1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'json'
+test.t1	analyze	status	OK
 SET @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch='mrr=on,mrr_sort_keys=on';
 SELECT * FROM t1
 WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
-timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
+timestamp >= DATE_ADD('2017-01-30 08:24:51', INTERVAL -1 MONTH)
 ORDER BY timestamp DESC;
 id	domain	registrant_name	registrant_organization	registrant_street1	registrant_street2	registrant_street3	registrant_street4	registrant_street5	registrant_city	registrant_postal_code	registrant_country	registrant_email	registrant_telephone	administrative_name	administrative_organization	administrative_street1	administrative_street2	administrative_street3	administrative_street4	administrative_street5	administrative_city	administrative_postal_code	administrative_country	administrative_email	administrative_telephone	technical_name	technical_organization	technical_street1	technical_street2	technical_street3	technical_street4	technical_street5	technical_city	technical_postal_code	technical_country	technical_email	technical_telephone	json	timestamp
+80551	www.mailhost.i-dev.fr	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL		2017-01-30 10:00:56
+80579	www.mailhost.i-dev.fr	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL		2017-01-30 10:00:56
+80594	www.mailhost.i-dev.fr	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL		2017-01-30 10:00:56
+80609	www.mailhost.i-dev.fr	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL		2017-01-30 10:00:56
 EXPLAIN EXTENDED SELECT * FROM t1
 WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
-timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
+timestamp >= DATE_ADD('2017-01-30 08:24:51', INTERVAL -1 MONTH)
 ORDER BY timestamp DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref|filter	ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp	ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp	767|4	const	2 (14%)	14.29	Using index condition; Using where; Using filesort; Using rowid filter
+1	SIMPLE	t1	ALL	ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp	NULL	NULL	NULL	60	22.22	Using where; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_street2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>(current_timestamp() + interval -1 month) order by `test`.`t1`.`timestamp` desc
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_street2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>('2017-01-30 08:24:51' + interval -1 month) order by `test`.`t1`.`timestamp` desc
 SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1;
 #
@@ -2738,6 +3380,10 @@ insert into filt(id,aceid,clid,fh) values
 (6341490487802728361,6341490487802728360,1,1291319099896431785),
 (6341490487802728362,6341490487802728360,1,8948400944397203540),
 (6341490487802728363,6341490487802728361,1,6701841652906431497);
+insert into filt select id+10000,aceid,clid,fh from filt;
+insert into filt select id+20000,aceid,clid,fh from filt;
+insert into filt select id+40000,aceid,clid,fh from filt;
+insert into filt select id+80000,aceid,clid,fh from filt;
 analyze table filt, acei, acli;
 Table	Op	Msg_type	Msg_text
 test.filt	analyze	status	Engine-independent statistics collected
@@ -2762,7 +3408,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t	index_merge	PRIMARY,acli_rid,acli_tp	acli_tp,acli_rid	2,767	NULL	2	100.00	Using intersect(acli_tp,acli_rid); Using where; Using index
 1	SIMPLE	a	ref	PRIMARY,acei_aclid	acei_aclid	8	test.t.id	1	100.00	Using where
-1	SIMPLE	fi	ref	filt_aceid,filt_fh	filt_aceid	8	test.a.id	1	17.14	Using where
+1	SIMPLE	fi	ref	filt_aceid,filt_fh	filt_aceid	8	test.a.id	24	14.46	Using where
 Warnings:
 Note	1003	select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
 set statement optimizer_switch='rowid_filter=off' for select t.id, fi.*
@@ -2777,6 +3423,36 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	id	aceid	clid	fh
 3080602882609775594	3080602882609775600	3080602882609775598	1	6311439873746261694
 3080602882609775594	3080602882609775601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925601	3080602882609775598	1	6311439873746261694
 set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.*
 from (acli t inner join acei a on a.aclid = t.id)
 inner join filt fi on a.id = fi.aceid
@@ -2789,7 +3465,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t	index_merge	PRIMARY,acli_rid,acli_tp	acli_tp,acli_rid	2,767	NULL	2	100.00	Using intersect(acli_tp,acli_rid); Using where; Using index
 1	SIMPLE	a	ref	PRIMARY,acei_aclid	acei_aclid	8	test.t.id	1	100.00	Using where
-1	SIMPLE	fi	ref|filter	filt_aceid,filt_fh	filt_aceid|filt_fh	8|8	test.a.id	1 (17%)	17.14	Using where; Using rowid filter
+1	SIMPLE	fi	ref|filter	filt_aceid,filt_fh	filt_aceid|filt_fh	8|8	test.a.id	24 (14%)	14.46	Using where; Using rowid filter
 Warnings:
 Note	1003	select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
 set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
@@ -2804,6 +3480,36 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	id	aceid	clid	fh
 3080602882609775594	3080602882609775600	3080602882609775598	1	6311439873746261694
 3080602882609775594	3080602882609775601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925601	3080602882609775598	1	6311439873746261694
 set optimizer_switch='mrr=on';
 set join_cache_level=6;
 set statement optimizer_switch='rowid_filter=off' for explain extended select t.id, fi.*
@@ -2818,7 +3524,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t	index_merge	PRIMARY,acli_rid,acli_tp	acli_tp,acli_rid	2,767	NULL	2	100.00	Using intersect(acli_tp,acli_rid); Using where; Using index
 1	SIMPLE	a	ref	PRIMARY,acei_aclid	acei_aclid	8	test.t.id	1	100.00	Using where; Using join buffer (flat, BKA join); Rowid-ordered scan
-1	SIMPLE	fi	ref	filt_aceid,filt_fh	filt_aceid	8	test.a.id	1	17.14	Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan
+1	SIMPLE	fi	ref	filt_aceid,filt_fh	filt_aceid	8	test.a.id	24	14.46	Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan
 Warnings:
 Note	1003	select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
 set statement optimizer_switch='rowid_filter=off' for select t.id, fi.*
@@ -2833,6 +3539,36 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	id	aceid	clid	fh
 3080602882609775594	3080602882609775600	3080602882609775598	1	6311439873746261694
 3080602882609775594	3080602882609775601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925601	3080602882609775598	1	6311439873746261694
 set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.*
 from (acli t inner join acei a on a.aclid = t.id)
 inner join filt fi on a.id = fi.aceid
@@ -2845,7 +3581,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t	index_merge	PRIMARY,acli_rid,acli_tp	acli_tp,acli_rid	2,767	NULL	2	100.00	Using intersect(acli_tp,acli_rid); Using where; Using index
 1	SIMPLE	a	ref	PRIMARY,acei_aclid	acei_aclid	8	test.t.id	1	100.00	Using where; Using join buffer (flat, BKA join); Rowid-ordered scan
-1	SIMPLE	fi	ref|filter	filt_aceid,filt_fh	filt_aceid|filt_fh	8|8	test.a.id	1 (17%)	17.14	Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter
+1	SIMPLE	fi	ref|filter	filt_aceid,filt_fh	filt_aceid|filt_fh	8|8	test.a.id	24 (14%)	14.46	Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter
 Warnings:
 Note	1003	select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
 set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
@@ -2860,6 +3596,36 @@ fi.fh in (6311439873746261694,-397087483897438286,
 id	id	aceid	clid	fh
 3080602882609775594	3080602882609775600	3080602882609775598	1	6311439873746261694
 3080602882609775594	3080602882609775601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609785601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609795601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609805601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609815601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609825601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609835601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609845601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609855601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609865601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609875601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609885601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609895601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609905601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609915601	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925600	3080602882609775598	1	6311439873746261694
+3080602882609775594	3080602882609925601	3080602882609775598	1	6311439873746261694
 set statement optimizer_switch='rowid_filter=on' for analyze format=json select t.id, fi.*
 from (acli t inner join acei a on a.aclid = t.id)
 inner join filt fi on a.id = fi.aceid
@@ -2940,23 +3706,24 @@ ANALYZE
             "key": "filt_fh",
             "used_key_parts": ["fh"]
           },
-          "rows": 6,
-          "selectivity_pct": 17.14285714,
-          "r_rows": 5,
+          "rows": 81,
+          "selectivity_pct": 14.46428571,
+          "r_rows": 80,
+          "r_lookups": 80,
           "r_selectivity_pct": 40,
           "r_buffer_size": "REPLACED",
           "r_filling_time_ms": "REPLACED"
         },
         "r_loops": 1,
-        "rows": 1,
-        "r_rows": 2,
+        "rows": 24,
+        "r_rows": 32,
         "r_table_time_ms": "REPLACED",
         "r_other_time_ms": "REPLACED",
-        "filtered": 17.1428566,
+        "filtered": 14.46428585,
         "r_filtered": 100
       },
       "buffer_type": "incremental",
-      "buffer_size": "603",
+      "buffer_size": "4Kb",
       "join_type": "BKA",
       "mrr_type": "Rowid-ordered scan",
       "attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)",
@@ -2975,38 +3742,99 @@ CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb;
 INSERT INTO t1 VALUES
 (1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'),
 (53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
 CREATE TABLE t2 (
-i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1)
+i1 int, c1 varchar(1) NOT NULL,
+filler1 char(255) default '0', filler2 char(255) default '0',
+KEY c1 (c1), KEY i1 (i1)
 ) engine=innodb;
-INSERT INTO t2 VALUES
-(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'),
-(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w');
+INSERT INTO t2(i1,c1) VALUES
+(NULL,'1'),(1,'1'),(2,'t'),(3,'1'),(4,'u'),(5,'1'),
+(6,'4'),(7,'4'),(8,'1'),(1,'u'),(2,'1'),(NULL,'w');
 INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+ANALYZE TABLE t1,t2 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
 SELECT * FROM t1
 WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
-WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
+WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5);
 pk	c1
+1	NULL
+15	o
+16	x
+19	t
+35	k
+36	h
+42	t
+43	h
+53	l
+62	a
+71	NULL
+79	u
+128	y
+129	NULL
+133	NULL
+1	NULL
+15	o
+16	x
+19	t
+35	k
+36	h
+42	t
+43	h
+53	l
+62	a
+71	NULL
+79	u
+128	y
+129	NULL
+133	NULL
+1	NULL
+15	o
+16	x
+19	t
+35	k
+36	h
+42	t
+43	h
+53	l
+62	a
+71	NULL
+79	u
+128	y
+129	NULL
+133	NULL
+1	NULL
 15	o
 16	x
 19	t
 35	k
 36	h
+42	t
 43	h
 53	l
 62	a
 71	NULL
+79	u
 128	y
 129	NULL
 133	NULL
 EXPLAIN EXTENDED SELECT * FROM t1
 WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
-WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
+WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ref|filter	c1,i1	c1|i1	3|5	func	6 (33%)	33.33	Using where; Full scan on NULL key; Using rowid filter
-2	DEPENDENT SUBQUERY	a1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	60	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ref|filter	c1,i1	c1|i1	3|5	func	38 (25%)	25.00	Using where; Full scan on NULL key; Using rowid filter
+2	DEPENDENT SUBQUERY	a1	ALL	NULL	NULL	NULL	NULL	60	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t1` `a1` where `test`.`t2`.`i1` = `test`.`t1`.`pk` and `test`.`t2`.`i1` is not null and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`))))
+Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t1` `a1` where `test`.`t2`.`i1` = `test`.`t1`.`pk` and `test`.`t2`.`i1` between 3 and 5 and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`))))
 DROP TABLE t1,t2;
 # End of 10.4 tests
diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test
index 3a31108387d..4255809bc24 100644
--- a/mysql-test/main/rowid_filter_innodb.test
+++ b/mysql-test/main/rowid_filter_innodb.test
@@ -32,6 +32,8 @@ insert into t1 values
   (85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160),
   (89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null);
 
+analyze table t1;
+
 let $q=
 ( select * from t1
     where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a')))
@@ -73,13 +75,13 @@ drop table t1, t2;
 
 create table t1 (a int, b int, key (b), key (a)) engine=innodb;
 insert into t1
-select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
+select (rand(1)*1000)/10, (rand(1001)*1000)/20 from seq_1_to_1000;
 analyze table t1;
 
 let $q=
-select count(*) from t1 where a in (22,83,11) and b=2;
+select count(*) from t1 where a between 21 and 30 and b=2;
 let $q1=
-select * from t1 where a in (22,83,11) and b=2;
+select * from t1 where a between 21 and 30 and b=2;
 
 set @save_optimizer_switch= @@optimizer_switch;
 
@@ -224,7 +226,7 @@ set global innodb_stats_persistent= @stats.save;
 
 CREATE TABLE t1 (
   id int(11) unsigned NOT NULL AUTO_INCREMENT,
-  domain varchar(255) NOT NULL,
+  domain varchar(32) NOT NULL,
   registrant_name varchar(255) DEFAULT NULL,
   registrant_organization varchar(255) DEFAULT NULL,
   registrant_street1 varchar(255) DEFAULT NULL,
@@ -317,6 +319,66 @@ technical_email, technical_telephone, json, timestamp) VALUES
  null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM',
  '492214307580', '', '2017-01-30 10:08:29');
 
+let $sqi=
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+VALUES
+('www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, '', '2016-12-22 09:18:28');
+
+eval $sqi;
+eval $sqi;
+eval $sqi;
+eval $sqi;
+eval $sqi;
+eval $sqi;
+eval $sqi;
+eval $sqi;
+
+let $qi=
+INSERT INTO t1 (
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp)
+SELECT
+domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp
+FROM t1;
+
+eval $qi;
+eval $qi;
+
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
 SET @save_optimizer_switch=@@optimizer_switch;
 
 SET optimizer_switch='mrr=on,mrr_sort_keys=on';
@@ -324,7 +386,7 @@ SET optimizer_switch='mrr=on,mrr_sort_keys=on';
 let $q=
 SELECT * FROM t1
   WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
-        timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
+        timestamp >= DATE_ADD('2017-01-30 08:24:51', INTERVAL -1 MONTH)
 ORDER BY timestamp DESC;
 
 eval $q;
@@ -497,6 +559,11 @@ insert into filt(id,aceid,clid,fh) values
 (6341490487802728362,6341490487802728360,1,8948400944397203540),
 (6341490487802728363,6341490487802728361,1,6701841652906431497);
 
+insert into filt select id+10000,aceid,clid,fh from filt;
+insert into filt select id+20000,aceid,clid,fh from filt;
+insert into filt select id+40000,aceid,clid,fh from filt;
+insert into filt select id+80000,aceid,clid,fh from filt;
+
 analyze table filt, acei, acli;
 
 let $q=
@@ -545,19 +612,28 @@ CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb;
 INSERT INTO t1 VALUES
 (1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'),
 (53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
 
 CREATE TABLE t2 (
-i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1)
+i1 int, c1 varchar(1) NOT NULL,
+filler1 char(255) default '0', filler2 char(255) default '0',
+KEY c1 (c1), KEY i1 (i1)
 ) engine=innodb;
-INSERT INTO t2 VALUES
-(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'),
-(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w');
+INSERT INTO t2(i1,c1) VALUES
+(NULL,'1'),(1,'1'),(2,'t'),(3,'1'),(4,'u'),(5,'1'),
+(6,'4'),(7,'4'),(8,'1'),(1,'u'),(2,'1'),(NULL,'w');
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
 INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+
+ANALYZE TABLE t1,t2 PERSISTENT FOR ALL;
 
 let $q=
 SELECT * FROM t1
 WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
-                      WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
+                      WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5);
 
 eval $q;
 eval EXPLAIN EXTENDED $q;
diff --git a/mysql-test/main/rowid_filter_innodb_debug.result b/mysql-test/main/rowid_filter_innodb_debug.result
index 6fd75294bdb..56226fe25ce 100644
--- a/mysql-test/main/rowid_filter_innodb_debug.result
+++ b/mysql-test/main/rowid_filter_innodb_debug.result
@@ -4,8 +4,6 @@ set default_storage_engine=innodb;
 #
 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;
 create table t2(a int);
 insert into t2 select A.a + B.a* 10 from t0 A, t0 B;
 CREATE TABLE t3 (
@@ -22,10 +20,10 @@ InnoDB
 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;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
@@ -38,7 +36,7 @@ where
 t3.key1=t2.a and t3.key2 in (2,3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	1000 (20%)	Using where; Using rowid filter
+1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	100 (2%)	Using where; Using rowid filter
 set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR go';
 select * from t2, t3
 where 
@@ -52,7 +50,7 @@ connection default;
 disconnect con1;
 ERROR 70100: Query execution was interrupted
 set debug_sync='RESET';
-drop table t0,t1,t2,t3;
+drop table t0,t2,t3;
 set default_storage_engine=default;
 set @save_optimizer_switch= @@optimizer_switch;
 set @save_use_stat_tables= @@use_stat_tables;
diff --git a/mysql-test/main/rowid_filter_myisam_debug.result b/mysql-test/main/rowid_filter_myisam_debug.result
index 16fcb2a416e..32a989f50da 100644
--- a/mysql-test/main/rowid_filter_myisam_debug.result
+++ b/mysql-test/main/rowid_filter_myisam_debug.result
@@ -3,8 +3,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;
 create table t2(a int);
 insert into t2 select A.a + B.a* 10 from t0 A, t0 B;
 CREATE TABLE t3 (
@@ -21,10 +19,10 @@ MyISAM
 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;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
@@ -37,7 +35,7 @@ where
 t3.key1=t2.a and t3.key2 in (2,3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	1000 (18%)	Using where; Using rowid filter
+1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	100 (2%)	Using where; Using rowid filter
 set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR go';
 select * from t2, t3
 where 
@@ -51,4 +49,4 @@ connection default;
 disconnect con1;
 ERROR 70100: Query execution was interrupted
 set debug_sync='RESET';
-drop table t0,t1,t2,t3;
+drop table t0,t2,t3;
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index 0f985c5d768..67923ef9be0 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using rowid filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using rowid filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1
 WHERE ID_better=1 AND ID1_with_null IS NULL AND 
 (ID2_with_null=1 OR ID2_with_null=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref|filter	idx1,idx2	idx1|idx2	5|4	const	2 (1%)	Using index condition; Using where; Using rowid filter
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	2	Using where
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index 91074287ad0..c1c70da9425 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3485,13 +3485,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3755,7 +3755,7 @@ EXPLAIN SELECT * FROM t1
 WHERE ID_better=1 AND ID1_with_null IS NULL AND 
 (ID2_with_null=1 OR ID2_with_null=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref|filter	idx1,idx2	idx1|idx2	5|4	const	2 (1%)	Using index condition; Using where; Using rowid filter
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	2	Using where
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index 0f985c5d768..67923ef9be0 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using rowid filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using rowid filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1
 WHERE ID_better=1 AND ID1_with_null IS NULL AND 
 (ID2_with_null=1 OR ID2_with_null=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref|filter	idx1,idx2	idx1|idx2	5|4	const	2 (1%)	Using index condition; Using where; Using rowid filter
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	2	Using where
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 40ab309fffd..72f16c09e97 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1641,8 +1641,8 @@ drop function f1;
 #
 create table t1 (a int, b int, key (b), key (a));
 insert into t1
-select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
-analyze table t1 ;
+select (rand(1)*1000)/10, (rand(1001)*1000)/20 from seq_1_to_1000;
+analyze table t1 persistent for all;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	Table is already up to date
@@ -1654,14 +1654,14 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5)
 explain extended select * from t1 use index () where b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	5.47	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	2.34	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2
 # Now, the equality is used for ref access, while the range condition
 # gives selectivity data
 explain extended select * from t1 where a in (17,51,5) and b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	58 (3%)	2.90	Using where; Using rowid filter
+1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	24 (3%)	2.90	Using where; Using rowid filter
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5)
 drop table t1;
@@ -1817,6 +1817,12 @@ create table t1 (id int, a int, PRIMARY KEY(id), key(a));
 insert into t1 select seq,seq from seq_1_to_100;
 create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
 insert into t2 select seq,seq,seq from seq_1_to_100;
+analyze table t1,t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
 set optimizer_switch='exists_to_in=off';
 set optimizer_use_condition_selectivity=2;
 SELECT * FROM t1
@@ -1850,7 +1856,7 @@ WHERE A.a=t1.a AND t2.b < 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
 2	DEPENDENT SUBQUERY	A	ref	PRIMARY,a	a	5	test.t1.a	1	
-2	DEPENDENT SUBQUERY	t2	ref|filter	a,b	a|b	5|5	test.A.id	1 (10%)	Using where; Using rowid filter
+2	DEPENDENT SUBQUERY	t2	ref	a,b	a	5	test.A.id	1	Using where
 EXPLAIN  SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	A	const	PRIMARY,a	PRIMARY	4	const	1	
@@ -1862,7 +1868,7 @@ WHERE A.a=t1.a AND t2.b < 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
 2	DEPENDENT SUBQUERY	A	ref	PRIMARY,a	a	5	test.t1.a	1	
-2	DEPENDENT SUBQUERY	t2	ref|filter	a,b	a|b	5|5	test.A.id	1 (10%)	Using where; Using rowid filter
+2	DEPENDENT SUBQUERY	t2	ref	a,b	a	5	test.A.id	1	Using where
 set optimizer_switch= @save_optimizer_switch;
 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
 drop table t1,t2;
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index 9c82a8d37a7..4e4513d09d6 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1111,8 +1111,8 @@ drop function f1;
 --echo #
 create table t1 (a int, b int, key (b), key (a));
 insert into t1
-select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
-analyze table t1 ;
+select (rand(1)*1000)/10, (rand(1001)*1000)/20 from seq_1_to_1000;
+analyze table t1 persistent for all;
 
 --echo # Check what info the optimizer has about selectivities
 explain extended select * from t1 use index () where a in (17,51,5);
@@ -1249,6 +1249,8 @@ insert into t1 select seq,seq from seq_1_to_100;
 create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
 insert into t2 select seq,seq,seq from seq_1_to_100;
 
+analyze table t1,t2 persistent for all;
+
 set optimizer_switch='exists_to_in=off';
 set optimizer_use_condition_selectivity=2;
 
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index a1d227a9baa..5b659e4b4be 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1651,8 +1651,8 @@ drop function f1;
 #
 create table t1 (a int, b int, key (b), key (a));
 insert into t1
-select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
-analyze table t1 ;
+select (rand(1)*1000)/10, (rand(1001)*1000)/20 from seq_1_to_1000;
+analyze table t1 persistent for all;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
@@ -1664,14 +1664,14 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5)
 explain extended select * from t1 use index () where b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	5.47	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	2.34	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2
 # Now, the equality is used for ref access, while the range condition
 # gives selectivity data
 explain extended select * from t1 where a in (17,51,5) and b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	59 (3%)	2.90	Using where; Using rowid filter
+1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	24 (3%)	2.90	Using where; Using rowid filter
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5)
 drop table t1;
@@ -1827,6 +1827,12 @@ create table t1 (id int, a int, PRIMARY KEY(id), key(a));
 insert into t1 select seq,seq from seq_1_to_100;
 create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
 insert into t2 select seq,seq,seq from seq_1_to_100;
+analyze table t1,t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
 set optimizer_switch='exists_to_in=off';
 set optimizer_use_condition_selectivity=2;
 SELECT * FROM t1
@@ -1860,7 +1866,7 @@ WHERE A.a=t1.a AND t2.b < 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	a	5	NULL	100	Using where; Using index
 2	DEPENDENT SUBQUERY	A	ref	PRIMARY,a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref|filter	a,b	a|b	5|5	test.A.id	1 (19%)	Using where; Using rowid filter
+2	DEPENDENT SUBQUERY	t2	ref	a,b	a	5	test.A.id	1	Using where
 EXPLAIN  SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	A	const	PRIMARY,a	PRIMARY	4	const	1	
@@ -1872,7 +1878,7 @@ WHERE A.a=t1.a AND t2.b < 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	a	5	NULL	100	Using where; Using index
 2	DEPENDENT SUBQUERY	A	ref	PRIMARY,a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref|filter	a,b	a|b	5|5	test.A.id	1 (19%)	Using where; Using rowid filter
+2	DEPENDENT SUBQUERY	t2	ref	a,b	a	5	test.A.id	1	Using where
 set optimizer_switch= @save_optimizer_switch;
 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
 drop table t1,t2;
diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h
index 467b6884ca6..b76b8b1e635 100644
--- a/sql/rowid_filter.h
+++ b/sql/rowid_filter.h
@@ -192,6 +192,9 @@ public:
   */
   virtual bool check(void *ctxt, char *elem) = 0;
 
+  /* True if the container does not contain any element */
+  virtual bool is_empty() = 0;
+
   virtual ~Rowid_filter_container() {}
 };
 
@@ -231,6 +234,8 @@ public:
 
   virtual ~Rowid_filter() {}
 
+  bool is_empty() { return container->is_empty(); }
+
   Rowid_filter_container *get_container() { return container; }
 
   void set_tracker(Rowid_filter_tracker *track_arg) { tracker= track_arg; }
@@ -268,6 +273,8 @@ public:
 
   bool check(char *elem)
   {
+    if (container->is_empty())
+      return false;
     bool was_checked= container->check(table, elem);
     tracker->increment_checked_elements_count(was_checked);
     return was_checked;
@@ -339,6 +346,8 @@ public:
     my_qsort2(array->front(), array->elements()/elem_size,
               elem_size, (qsort2_cmp) cmp, cmp_arg);
   }
+
+  bool is_empty() { return elements() == 0; }
 };
 
 
@@ -368,6 +377,8 @@ public:
   bool add(void *ctxt, char *elem) { return refpos_container.add(elem); }
 
   bool check(void *ctxt, char *elem);
+
+  bool is_empty() { return refpos_container.is_empty(); }
 };
 
 /**
diff --git a/sql/sql_analyze_stmt.h b/sql/sql_analyze_stmt.h
index 990c79fb9ad..968b8d38295 100644
--- a/sql/sql_analyze_stmt.h
+++ b/sql/sql_analyze_stmt.h
@@ -416,12 +416,13 @@ public:
 
   uint get_container_elements() const { return container_elements; }
 
+  uint get_container_lookups() { return n_checks; }
+
   double get_r_selectivity_pct() const
   {
-    return static_cast<double>(n_positive_checks) /
-      static_cast<double>(n_checks);
+    return n_checks ? static_cast<double>(n_positive_checks) /
+                      static_cast<double>(n_checks) : 0;
   }
 
   size_t get_container_buff_size() const { return container_buff_size; }
 };
-
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 539e427ee2f..1b59dce10b9 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -1676,6 +1676,7 @@ void Explain_rowid_filter::print_explain_json(Explain_query *query,
   if (is_analyze)
   {
     writer->add_member("r_rows").add_double(tracker->get_container_elements());
+    writer->add_member("r_lookups").add_ll(tracker->get_container_lookups());
     writer->add_member("r_selectivity_pct").
       add_double(tracker->get_r_selectivity_pct() * 100.0);
     writer->add_member("r_buffer_size").
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 81c4991e801..3d880365d42 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7496,9 +7496,11 @@ best_access_path(JOIN      *join,
   double best=              DBL_MAX;
   double best_time=         DBL_MAX;
   double records=           DBL_MAX;
+  ha_rows records_for_key=   0;
   table_map best_ref_depends_map= 0;
   Range_rowid_filter_cost_info *best_filter= 0;
   double tmp;
+  double keyread_tmp= 0;
   ha_rows rec;
   bool best_uses_jbuf= FALSE;
   MY_BITMAP *eq_join_set= &s->table->eq_join_set;
@@ -7772,8 +7774,12 @@ best_access_path(JOIN      *join,
             /* Limit the number of matched rows */
             tmp= cost_for_index_read(thd, table, key, (ha_rows) records,
                                      (ha_rows) s->worst_seeks);
+            records_for_key= (ha_rows) records;
+            set_if_smaller(records_for_key, thd->variables.max_seeks_for_key);
+            keyread_tmp= table->file->keyread_time(key, 1, records_for_key);
         got_cost:
             tmp= COST_MULT(tmp, record_count);
+            keyread_tmp= COST_MULT(keyread_tmp, record_count);
           }
         }
         else
@@ -7950,12 +7956,14 @@ best_access_path(JOIN      *join,
             }
 
             /* Limit the number of matched rows */
-            tmp= records;
-            set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
-            tmp= cost_for_index_read(thd, table, key, (ha_rows) tmp,
+            tmp= cost_for_index_read(thd, table, key, (ha_rows) records,
                                      (ha_rows) s->worst_seeks);
+            records_for_key= (ha_rows) records;
+            set_if_smaller(records_for_key, thd->variables.max_seeks_for_key);
+            keyread_tmp= table->file->keyread_time(key, 1, records_for_key);
         got_cost2:
             tmp= COST_MULT(tmp, record_count);
+            keyread_tmp= COST_MULT(keyread_tmp, record_count);
           }
           else
           {
@@ -7974,7 +7982,35 @@ best_access_path(JOIN      *join,
 	  (found_part & 1))   // start_key->key can be used for index access
       {
         double rows= record_count * records;
-        double access_cost_factor= MY_MIN(tmp / rows, 1.0);
+
+        /*
+          If we use filter F with selectivity s the the cost of fetching data
+          by key using this filter will be
+             cost_of_fetching_1_row * rows * s +
+             cost_of_fetching_1_key_tuple * rows * (1 - s) +
+             cost_of_1_lookup_into_filter * rows
+          Without using any filter the cost would be just
+             cost_of_fetching_1_row * rows
+
+          So the gain in access cost per row will be
+             cost_of_fetching_1_row * (1 - s) -
+             cost_of_fetching_1_key_tuple * (1 - s) -
+             cost_of_1_lookup_into_filter
+             =
+             (cost_of_fetching_1_row - cost_of_fetching_1_key_tuple) * (1 - s)
+             - cost_of_1_lookup_into_filter
+
+          Here we have:
+             cost_of_fetching_1_row = tmp/rows
+             cost_of_fetching_1_key_tuple = keyread_tmp/rows
+
+          Note that access_cost_factor may be greater than 1.0. In this case
+          we still can expect a gain of using rowid filter due to smaller number
+          of checks for conditions pushed to the joined table.
+	*/
+        double rows_access_cost= MY_MIN(rows, s->worst_seeks);
+        double access_cost_factor= MY_MIN((rows_access_cost - keyread_tmp) /
+                                           rows, 1.0);
         filter=
           table->best_range_rowid_filter_for_partial_join(start_key->key, rows,
                                                           access_cost_factor);
@@ -8135,6 +8171,10 @@ best_access_path(JOIN      *join,
         double rows= record_count * s->found_records;
         double access_cost_factor= MY_MIN(tmp / rows, 1.0);
         uint key_no= s->quick->index;
+
+        /* See the comment concerning using rowid filter for with ref access */
+        keyread_tmp= s->table->opt_range[key_no].index_only_cost;
+        access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0);
         filter=
         s->table->best_range_rowid_filter_for_partial_join(key_no, rows,
                                                            access_cost_factor);
@@ -20929,6 +20969,8 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
     DBUG_RETURN(NESTED_LOOP_ERROR);
 
   join_tab->build_range_rowid_filter_if_needed();
+  if (join_tab->rowid_filter && join_tab->rowid_filter->is_empty())
+    rc= NESTED_LOOP_NO_MORE_ROWS;
 
   join->return_tab= join_tab;