drop table if exists t1,t2;
create table t1 (a int, b int);
insert into t1 values (3, 30), (4, 20), (1, 20);
create table t2 (c int, d int, v int as (d+1), index idx(c));
insert into t2(c,d) values 
(20, 100), (20, 300), (30, 100), (30, 200), (40, 500),
(70, 100), (40, 300), (60, 100), (40, 100), (70, 100);
set join_cache_level=6;
explain
select * from t1,t2 where t1.b=t2.c and d <= 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	2	Using where; Using join buffer
select * from t1,t2 where t1.b=t2.c and d <= 100;
a	b	c	d	v
4	20	20	100	101
1	20	20	100	101
3	30	30	100	101
set join_cache_level=default;
drop table t1, t2;