Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22191

Range access is not picked when index_merge_sort_union is turned off

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: