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;