Seven-fold performance regression with maria-5.3-dsmrr-cpk

[Philip Stoev]

The following query:

SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;

Takes less than 0.1 seconds on maria-5.3 and more than 0.7 seconds on maria-5.3-dsmrr-cpk with mrr_sort_keys=ON, and many minutes with maria_sort_keys=OFF.

The query demonstrates both A) the performance regression as a ratio between the current running time and the new running time and B) the fact that a previously instantaneous query now takes a period of time that would be noticed by the user.

[Sergey Petrunia (sergefp)]

Trying on the latest 5.3-dsmrr-cpk and 5.3-main, compiled with BUILD/compile-pentium-max, I got this:

5.3-main
--------

MariaDB [bug637160]> explain SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;
+----+-------------+--------+--------+-------------------------+-------------+---------+--------------------------------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+-------------------------+-------------+---------+--------------------------------+------+-------------------+
| 1 | SIMPLE | table1 | index | NULL | col_int_key | 5 | NULL | 1251 | Using index |
| 1 | SIMPLE | table3 | ALL | NULL | NULL | NULL | NULL | 1251 | Using join buffer |
| 1 | SIMPLE | table2 | eq_ref | PRIMARY,col_varchar_key | PRIMARY | 4 | bug637160.table3.col_int_nokey | 1 | Using where |
+----+-------------+--------+--------+-------------------------+-------------+---------+--------------------------------+------+-------------------+
(The same with @@join_cache_level=6)

Average query execution time is 0.22 sec

5.3-dsmrr-cpk
-------------

MariaDB [bug637160]> explain SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;
+----+-------------+--------+--------+-------------------------+-------------+---------+--------------------------------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+-------------------------+-------------+---------+--------------------------------+------+-------------------+
| 1 | SIMPLE | table1 | index | NULL | col_int_key | 5 | NULL | 874 | Using index |
| 1 | SIMPLE | table3 | ALL | NULL | NULL | NULL | NULL | 874 | Using join buffer |
| 1 | SIMPLE | table2 | eq_ref | PRIMARY,col_varchar_key | PRIMARY | 4 | bug637160.table3.col_int_nokey | 1 | Using where |
+----+-------------+--------+--------+-------------------------+-------------+---------+--------------------------------+------+-------------------+
MariaDB [bug637160]> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)

MariaDB [bug637160]> explain SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` )...

[Igor Babaev (igorb-seattle)]

This cannot be considered as a performance regression.
The fact is that with BKA join first join buffer is refilled with the combinations of tables t1,t3 , and only after this the records are read from the buffer. There are 1000*1000 such combinations and only the first 8786 are sent to the result set.
So it does not make sense to use BKA join for this query at all.

The future implementation of the fair choice between block-based join algorithms and simple join algorithms should take into
account the limit clause.