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

Table elimination is not used for tables inside semi-join

    XMLWordPrintable

Details

    Description

      Table elimination is not applied inside semi-joins. This has adverse effects in a number of cases.

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

      explain select * from t0 
      where t0.a in (
        select t1.a 
        from t1 
         left join t2 on t2.pk=t1.b
         left join t3 on t3.pk=t1.b
        );

      Gives:

      +------+-------------+-------+--------+---------------+---------+---------+----------+------+------------------------------------------------------------------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref      | rows | Extra                                                            |
      +------+-------------+-------+--------+---------------+---------+---------+----------+------+------------------------------------------------------------------+
      |    1 | PRIMARY     | t0    | ALL    | NULL          | NULL    | NULL    | NULL     |   10 |                                                                  |
      |    1 | PRIMARY     | t1    | ALL    | NULL          | NULL    | NULL    | NULL     |   10 | Using where; Start temporary; Using join buffer (flat, BNL join) |
      |    1 | PRIMARY     | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | j22.t1.b |    1 | Using where; Using index                                         |
      |    1 | PRIMARY     | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | j22.t1.b |    1 | Using where; Using index; End temporary                          |
      +------+-------------+-------+--------+---------------+---------+---------+----------+------+------------------------------------------------------------------+

      An easy way to check that table elimination is applicable:

      set optimizer_switch='semijoin=off';
      explain ...
       
      +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | PRIMARY            | t0    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
      |    2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
      +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
       

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              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.