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

Unexpected index intersection with full index scan for an index

    XMLWordPrintable

    Details

      Description

      For the table

      CREATE TABLE t1 (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        p char(32) DEFAULT NULL,
        es tinyint(3) unsigned NOT NULL DEFAULT 0,
        er tinyint(3) unsigned NOT NULL DEFAULT 0,
        x mediumint(8) unsigned NOT NULL DEFAULT 0,
        PRIMARY KEY (id),
        INDEX es (es),
        INDEX x (x),
        INDEX er (er,x),
        INDEX p (p)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      

      populated with the statements:

      insert into t1(es,er) select 0, 1 from seq_1_to_45;
      insert into t1(es,er) select 0, 2 from seq_1_to_49;
      insert into t1(es,er) select 0, 3 from seq_1_to_951;
      insert into t1(es,er) select 0, 3 from seq_1_to_1054;
      insert into t1(es,er) select 0, 6 from seq_1_to_25;
      insert into t1(es,er) select 0, 11 from seq_1_to_1;
      insert into t1(es,er) select 1, 1 from seq_1_to_45;
      insert into t1(es,er) select 1, 2 from seq_1_to_16;
      insert into t1(es,er) select 1, 3 from seq_1_to_511;
      insert into t1(es,er) select 1, 4 from seq_1_to_687;
      insert into t1(es,er) select 1, 6 from seq_1_to_50;
      insert into t1(es,er) select 1, 7 from seq_1_to_4;
      insert into t1(es,er) select 1, 11 from seq_1_to_1;
      insert into t1(es,er) select 2, 1 from seq_1_to_82;
      insert into t1(es,er) select 2, 2 from seq_1_to_82;
      insert into t1(es,er) select 2, 3 from seq_1_to_1626;
      insert into t1(es,er) select 2, 4 from seq_1_to_977;
      insert into t1(es,er) select 2, 6 from seq_1_to_33;
      insert into t1(es,er) select 2, 11 from seq_1_to_1;
      insert into t1(es,er) select 3, 1 from seq_1_to_245;
      insert into t1(es,er) select 3, 2 from seq_1_to_81;
      insert into t1(es,er) select 3, 3 from seq_1_to_852;
      insert into t1(es,er) select 3, 4 from seq_1_to_2243;
      insert into t1(es,er) select 3, 6 from seq_1_to_44;
      insert into t1(es,er) select 3, 11 from seq_1_to_1;
      insert into t1(es,er) select 4, 1 from seq_1_to_91;
      insert into t1(es,er) select 4, 2 from seq_1_to_83;
      insert into t1(es,er) select 4, 3 from seq_1_to_297;
      insert into t1(es,er) select 4, 4 from seq_1_to_2456;
      insert into t1(es,er) select 4, 6 from seq_1_to_19;
      insert into t1(es,er) select 4, 11 from seq_1_to_1;
      update t1 set p='foobar';
      update t1 set x=0;
      

      the following execution plan is chosen for the query

      SELECT * FROM t1
        WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
      

      if the optimizer switch index_merge_sort_intersection is set to 'on'

      EXPLAIN EXTENDED SELECT * FROM t1 WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) limit 2;
      +------+-------------+-------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+
      | id   | select_type | table | type        | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                    |
      +------+-------------+-------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+
      |    1 | SIMPLE      | t1    | index_merge | es,er,p       | er,es | 0,1     | NULL | 1852 |   100.00 | Using sort_intersect(er,es); Using where |
      +------+-------------+-------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+
      

      As the range condition for the index 'er' (er!=4 OR er=4) is always true this index is of no use for index intersection.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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