Details
Description
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB; |
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
MariaDB [test]> SET OPTIMIZER_SWITCH="index_merge_sort_union=ON";
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain SELECT * FROM t1 WHERE a > 5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 4 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> SET OPTIMIZER_SWITCH="index_merge_sort_union=OFF";
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain SELECT * FROM t1 WHERE a > 5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | a | a | 5 | NULL | 10 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
So as you see above the plan changed from range scan to index scan when we turned off index_merge_sort_union, which is absolutely WRONG. This is because of the fix for MDEV-21932
Attachments
Issue Links
- duplicates
-
MDEV-22174 Assertion `cost > 0.0' failed in adjust_quick_cost
- Closed
- relates to
-
MDEV-22160 SIGSEGV in st_join_table::save_explain_data on SELECT
- Closed
-
MDEV-21932 A fast plan with ROR index-merge is ignored when 'index_merge_sort_union=off'
- Closed