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

Unnecessary "Range checked for each record" leads to a poor performance

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.1.67
    • 5.1.73
    • None
    • None

    Description

      The optimizer produces an expected execution plan for the query:

        select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020);
      MariaDB [test]> explain
          -> select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020);
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |  1 | SIMPLE      | t2    | ALL   | NULL          | NULL | NULL    | NULL |   64 |             |
      |  1 | SIMPLE      | t1    | range | i1            | i1   | 4       | NULL |   78 | Using where |
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

      While for the query:
      select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
      the produced plan employs unnecessary "Range checked for each record":

      MariaDB [test]> explain
          -> select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
      +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                          |
      +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
      |  1 | SIMPLE      | t2    | ALL   | NULL          | NULL | NULL    | NULL |   64 |                                                |
      |  1 | SIMPLE      | t1    | range | i2            | i1   | 4       | NULL |   78 | Range checked for each record (index map: 0x2) |
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------

      This results in an extremely poor performance on the second query.

      In interesting is that for the second query for each record actually the same range scan of t1 is used, and this is exactly the scan chosen for the first query.

      The problem can be reproduced with the following test case:

      create table t1( key1 int not null, INDEX i1(key1) );
      insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
      insert into t1 select key1+8 from t1; 
      insert into t1 select key1+16 from t1; 
      insert into t1 select key1+32 from t1; 
      insert into t1 select key1+64 from t1; 
      insert into t1 select key1+128 from t1; 
      insert into t1 select key1+256 from t1; 
      insert into t1 select key1+512 from t1; 
       
      alter table t1 add key2 int not null, add index i2(key2);
      update t1 set key2=key1;
      analyze table t1;
       
      create table t2 (a int);
      insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
      insert into t2 select a+16 from t2;
      insert into t2 select a+32 from t2;
      insert into t2 select a+64 from t2;
       
      explain
      select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020);
      select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020);
      explain
      select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
      select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
       
      drop table t1,t2;

      The problem is reproducible in any release of MariaDB of 5.1 and in any release of the higher versions.
      The problem can be observed in any release of MySQL 5.1/5.5/5.6 as well
      I even successfully reproduced the problem in MySQL 4.1

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.