Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
None
-
None
-
None
Description
The following query in MySQL 5.0.72sp1 correctly uses the index_merge optimization and executes in less than a second. On MariaDB it doesn't use the index_merge optimization and instead does a full scan of t3. The attached file fast_in_mysql_slow_in_mariadb.sql contains the tables to reproduce the bug. I've tested this in MariaDB 5.2.7 as well as 5.2.9. Uncommenting the index hint in mariadb gives the desired behavior.
SELECT
*
FROM t1
LEFT JOIN t2 /* use index (primary, c3) */
ON t2.c2 = t1.c2
LEFT JOIN t3
ON t2.c1 = t3.c1
WHERE
((t2.c2 <=> 182104825 OR t2.c3 <=> 182104825)) AND
(t3.c1 IS NOT NULL)
Explain output from MariaDB:
--------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t3 | index | PRIMARY | PRIMARY | 8 | NULL | 99879 | Using where; Using index |
| 1 | SIMPLE | t2 | ref | PRIMARY,c1,c3 | c1 | 9 | test2.t3.c1 | 1 | Using where |
| 1 | SIMPLE | t1 | ref | c2 | c2 | 9 | test2.t2.c2 | 1 | Using where; Using index |
--------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
Desired explain output from MySQL:
----------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t2 | index_merge | PRIMARY,c1,c3 | PRIMARY,c3 | 8,8 | NULL | 2 | Using union(PRIMARY,c3); Using where |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 8 | test2.t2.c1 | 1 | Using where; Using index |
| 1 | SIMPLE | t1 | ref | c2 | c2 | 9 | test2.t2.c2 | 1 | Using where; Using index |
----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)