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

Unexpected plan with index merge where range scan should be used

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2
    • 10.2
    • Optimizer
    • None

    Description

      For the following table:

      create table t2(
        d int, e int, key idx1(d), key idx2(e), f varchar(32)
      ) engine=myisam;
      insert into t2 values
       (4,5,'a'), (7,8,'b'), (4,3,'c'), (1,2,'d'), (2,1,'e'), (5,5,'f'),
       (2,3,'g'), (7,7,'h'), (3,3,'i'), (3,2,'j'), (2,2,'k'), (5,5,'l'),
       (4,5,'aa'), (7,8,'bb'), (4,3,'cc'), (1,2,'dd'), (2,1,'ee'), (5,5,'ff'),
       (2,3,'gg'), (7,7,'hh'), (3,3,'ii'), (3,2,'jj'), (2,2,'kk'), (5,5,'ll'),
       (4,5,'aaa'), (7,8,'bbb'), (4,3,'ccc'), (1,2,'ddd'), (2,1,'eee'), (5,5,'fff'),
       (2,3,'ggg'), (7,7,'hhh'), (3,3,'iii'), (3,2,'jjj'), (2,2,'kkk'), (5,5,'lll'),
       (14,15,'a'), (17,18,'b'), (14,13,'c'), (11,12,'d'), (12,11,'e'), (15,15,'f'),
       (12,13,'g'), (17,17,'h'), (13,13,'i'), (13,12,'j'), (12,12,'k'), (15,15,'l'),
       (24,25,'a'), (27,28,'b'), (24,23,'c'), (21,22,'d'), (22,21,'e'), (25,25,'f'),
       (22,23,'g'), (27,27,'h'), (23,23,'i'), (23,22,'j'), (22,22,'k'), (25,25,'l'),
       (34,35,'a'), (37,38,'b'), (34,33,'c'), (31,32,'d'), (32,31,'e'), (35,35,'f'),
       (32,33,'g'), (37,37,'h'), (33,33,'i'), (33,32,'j'), (32,32,'k'), (35,35,'l'),
       (44,45,'a'), (47,48,'b'), (44,43,'c'), (41,42,'d'), (42,41,'e'), (45,45,'f'),
       (42,43,'g'), (47,47,'h'), (43,43,'i'), (43,42,'j'), (42,42,'k'), (45,45,'l');
      

      this query

      select * from t2 where d=3 and e=3 or d=7 and e=7;
      

      is executed by the plan that utilizes index merge:

      MariaDB [test]> explain select * from t2 where d=3 and e=3 or d=7 and e=7;
      +------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
      | id   | select_type | table | type        | possible_keys | key       | key_len | ref  | rows | Extra                               |
      +------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
      |    1 | SIMPLE      | t2    | index_merge | idx1,idx2     | idx1,idx2 | 5,5     | NULL |    9 | Using union(idx1,idx2); Using where |
      +------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
      

      If we prohibit to use index merge

      MariaDB [test]> set optimizer_switch='index_merge=off';
      

      then we see that there is a range scan over the index idx1 used by the index merge.
      MariaDB [test]> explain select * from t2 where d=3 and e=3 or d=7 and e=7;
      ---------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ---------------------------------------------------------------------------------------------+

      1 SIMPLE t2 range idx1,idx2 idx1 5 NULL 12 Using index condition; Using where

      ---------------------------------------------------------------------------------------------+
      This range scan cannot read more records than the index merge, though the explain outputs
      mislead us here..

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.