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

Semi-join optimization is not used inside outer join nest

    XMLWordPrintable

    Details

      Description

      Test dataset:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table ten2 as select * from ten;
       
      create table t1(a int, b int, key(a));
      insert into t1 
      select 
        A.a + B.a* 10 + C.a * 100,
        A.a + B.a* 10 + C.a * 100
      from 
        ten A, ten B, ten C;
       
      create table t2 like t1;
      insert into t2 select * from t1;
      

      The query is constructed as follows:

      • a non-reducible nested outer join (multiple tables on the inner side)
      • the nested outer join has a semi-join inside it
      • the data distribution is such that an inner-to-outer join order inside the
        join nest would be beneficial: putting the subquery first would allow to use
        a good index on t2.a, and read 10*1=10 rows from there as opposed to 1K rows
        it has in total

      explain
      select * 
      from 
        ten left join
        (t1, t2) on (t2.a in (select ten2.a from ten2) and 
                     t2.b=t1.b and 
                     t1.b=ten.a);
      

      Explain:

      +------+--------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
      | id   | select_type  | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
      +------+--------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
      |    1 | PRIMARY      | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                        |
      |    1 | PRIMARY      | t1    | ALL  | NULL          | NULL | NULL    | NULL |  760 | Using where; Using join buffer (flat, BNL join)        |
      |    1 | PRIMARY      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 1088 | Using where; Using join buffer (incremental, BNL join) |
      |    2 | MATERIALIZED | ten2  | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                        |
      +------+--------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
      

      We can see that ten2.id=2, that is, the subquery was not converted into a semi-join.

      EXPLAIN in mysql 8:

      explain select *  from    ten left join   (t1, t2) on (t2.a in (select ten2.a from ten2) and t1.b=t2.b and t1.b=ten.a);
      +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+------------------------------+
      | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref       | rows | filtered | Extra                        |
      +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+------------------------------+
      |  1 | SIMPLE      | ten   | NULL       | ALL  | NULL          | NULL | NULL    | NULL      |   10 |   100.00 | NULL                         |
      |  1 | SIMPLE      | ten2  | NULL       | ALL  | NULL          | NULL | NULL    | NULL      |   10 |   100.00 | Using where; Start temporary |
      |  1 | SIMPLE      | t2    | NULL       | ref  | a             | a    | 5       | j2.ten2.a |    1 |   100.00 | Using where; End temporary   |
      |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL      | 1000 |   100.00 | Using where                  |
      +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+------------------------------+
      

      here ten2.id=1, and it is put before the table t2, which has t2.type=ref with rows=1 (efficient!)

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.