MariaDB [(none)]> select version();

+----------------+

| version()      |

+----------------+

| 10.4.3-MariaDB |

+----------------+

1 row in set (0.000 sec)

MariaDB [(none)]> 

set optimizer_switch="rowid_filter=on";

create table t1 (id int not null  primary key) engine=innodb;

insert into t1 values (2),(1);

create table t2 (y int,x int,index (x),index (y)) engine=innodb;

insert into t2 values  (4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1),(555,555),(666,1);

select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1;

MariaDB [test]> select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1;

Empty set (0.001 sec)

MariaDB [test]> analyze

    -> select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1;

+------+-------------+-------+------------+---------------+---------+---------+-------+---------+------------+----------+------------+---------------------------------+

| id   | select_type | table | type       | possible_keys | key     | key_len | ref   | rows    | r_rows     | filtered | r_filtered | Extra                           |

+------+-------------+-------+------------+---------------+---------+---------+-------+---------+------------+----------+------------+---------------------------------+

|    1 | SIMPLE      | t1    | const      | PRIMARY       | PRIMARY | 4       | const | 1       | NULL       |   100.00 |       NULL | Using index                     |

|    1 | SIMPLE      | t2    | ref|filter | x,y           | x|y     | 5|5     | const | 6 (20%) | 1.00 (16%) |    55.56 |       0.00 | Using where; Using rowid filter |

+------+-------------+-------+------------+---------------+---------+---------+-------+---------+------------+----------+------------+---------------------------------+

2 rows in set (0.000 sec)

MariaDB [test]> select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1;

+----+------+------+

| id | y    | x    |

+----+------+------+

|  1 |    2 |    1 |

+----+------+------+

1 row in set (0.001 sec)

[mysqld]

optimizer_switch=rowid_filter=off

MariaDB [(none)]>  SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on

1 row in set (0.001 sec)

MariaDB [(none)]> quit