The above examples will look as follows:
MariaDB [test]> explain select * from t1 where key1<30;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
| 1 | SIMPLE | t1 | range | key1 | key1 | 5 | NULL
| 1 | Using index condition; Sort keys; Sort rowids |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
MRR with BKA are shown like this:
MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1;
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL
| 10 | Using where |
| 1 | SIMPLE | t1 | ref | key1 | key1 | 5 |
test.t2.a | 1 | Using join buffer (flat, BKA join); Sort keys; Sort
rowids |
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+
Q: any comments?
Less obvious suggestions:
3. It would be useful to have an idea about whether DS-MRR had sufficient
buffer space to operate. One can get a rough picture by adding counters of
key/rowid sort operations:
- Handler_mrr_rowid_sort_count
- Handler_mrr_key_sort_count
If Handler_mrr_key_sort_count == Handler_multi_range_read_init_count then
we've
had enough buffer space. If key_sort_count is two times greater than the
number
of init operations, then one could conclude that on average two buffer
refills
were needed and increasing buffer size up to 2x would be beneficial.
