|
Create and populate tables t0,t1,t2 as in MDEV-21625.
The tables t0 and t1 are of the same structure and populated with the same set of rows. The only difference is that t1 is partitioned.
Execute the commands
set join_cache_level=6;
|
set optimizer_switch='mrr=on';
|
You'll see that for the query:
select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
|
index condition pushdown is empoyed when joining table t0
MariaDB [test]> explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-------------------------------------------------------------------------------+
|
| 1 | SIMPLE | t2 | range | idx | idx | 5 | NULL | 2 | 100.00 | Using where; Using index |
|
| 1 | SIMPLE | t0 | ref | idx | idx | 5 | test.t2.a | 13 | 100.00 | Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-------------------------------------------------------------------------------+
|
while for the similar query that uses tables t1,t2
select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4
|
no index condition pushdown is employed
MariaDB [test]> explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+---------------------------------------------------------------------+
|
| 1 | SIMPLE | t2 | range | idx | idx | 5 | NULL | 2 | 100.00 | Using where; Using index |
|
| 1 | SIMPLE | t1 | ref | idx | idx | 5 | test.t2.a | 5 | 100.00 | Using where; Using join buffer (flat, BKA join); Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+---------------------------------------------------------------------+
|
Setting a breakpoint in handler_index_cond_check confirms the above.
Interesting that if to make the pushable condition in the second query dependent on table t2
select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1
|
then the optimizer chooses using index condition pushdown
MariaDB [test]> explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+
|
| 1 | SIMPLE | t2 | range | idx | idx | 5 | NULL | 2 | 100.00 | Using where; Using index |
|
| 1 | SIMPLE | t1 | ref | idx | idx | 5 | test.t2.a | 5 | 100.00 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+
|
though the execution of the query is incorrect due to the bug MDEV-21554.
|