Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5(EOL)
-
None
-
None
-
None
-
10.4 and 10.7 were tested on a ubuntu 18.04 server
10.5 and 10.6 were tested on a ubuntu 20.04 desktop
Description
if you take the following testcase and execute it on mariadb 10.4 and 10.5+ you can see the different join conditions.
The path chosen in 10.4 is the fastest in our case, the path chosen in 10.5 increases the query time from 1.6 seconds to 30+ seconds (in our actual query)
I forced the join index to be the right one and it dropped back down again.
DROP TABLE IF EXISTS `test_join_a`; |
CREATE TABLE `test_join_a` ( |
`a_id` bigint(20) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`a_id`) |
) ENGINE=InnoDB;
|
|
|
DROP TABLE IF EXISTS `test_join_b`; |
CREATE TABLE `test_join_b` ( |
`b_id` bigint(20) NOT NULL AUTO_INCREMENT, |
`b_a_id` bigint(20) DEFAULT NULL, |
`b_date_start` date DEFAULT NULL, |
`b_date_end` date DEFAULT NULL, |
PRIMARY KEY (`b_id`), |
KEY `ix_b_date_start` (`b_date_start`), |
KEY `ix_b_date_end` (`b_date_end`), |
KEY `ix_b_a_id` (`b_a_id`) |
) ENGINE=InnoDB;
|
|
|
EXPLAIN SELECT |
a_id
|
FROM test_join_a a |
LEFT JOIN test_join_b b ON a.a_id = b.b_a_id AND |
(b.b_date_start IS NULL OR b.b_date_start <= CURRENT_DATE) AND |
(b.b_date_end IS NULL OR b.b_date_end > CURRENT_DATE) |
;
|
on 10.4 it outputs:
+------+-------------+-------+-------+-----------------------------------------+-----------+---------+--------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+-----------------------------------------+-----------+---------+--------+------+-------------+
|
| 1 | SIMPLE | a | index | NULL | PRIMARY | 8 | NULL | 1 | Using index |
|
| 1 | SIMPLE | b | ref | ix_b_date_start,ix_b_date_end,ix_b_a_id | ix_b_a_id | 9 | a.a_id | 1 | Using where |
|
+------+-------------+-------+-------+-----------------------------------------+-----------+---------+--------+------+-------------+
|
on 10.5, 10.6 and 10.7 it shows:
+------+-------------+-------+-------+-----------------------------------------+-----------------+---------+------+------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+-----------------------------------------+-----------------+---------+------+------+------------------------------------------------+
|
| 1 | SIMPLE | a | index | NULL | PRIMARY | 8 | NULL | 1 | Using index |
|
| 1 | SIMPLE | b | ALL | ix_b_date_start,ix_b_date_end,ix_b_a_id | ix_b_date_start | 4 | NULL | 1 | Range checked for each record (index map: 0xE) |
|
+------+-------------+-------+-------+-----------------------------------------+-----------------+---------+------+------+------------------------------------------------+
|