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

Slow performance with outer joins and join buffer.

Details

    Description

      A query over empty tables is extremely slow when outer join is used together with join buffering (which is ON by default).

      Take this file

      drop table if exists t2,t1;
      		 
       
      		 
      CREATE TABLE `t1` (
      		 
        `id` int(11) NOT NULL AUTO_INCREMENT,
      		 
        `col1` varchar(255) NOT NULL DEFAULT '',
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;
      		 
       
      		 
      CREATE TABLE `t2` (
      		 
        `id` int(11) NOT NULL AUTO_INCREMENT,
      		 
        `parent_id` smallint(3) NOT NULL DEFAULT '0',
      		 
        `col2` varchar(25) NOT NULL DEFAULT '',
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;
      		 
       
      		 
      select now();
      		 
      SELECT t.* 
      FROM 
        t1 t 
        LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
      		 
        LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
      		 
        LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
      		 
        LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
      		 
        LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
      		 
        LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
      		 
        LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
      		 
        LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
      		 
        LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
      		 
        LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
      		 
        LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
      		 
        LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
      		 
        LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
      		 
        LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
      		 
        LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
      		 
        LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
      		 
        LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
      		 
        LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
      		 
        LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
      		 
        LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
      		 
        LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
      		 
        LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
      		 
        LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
      		 
        LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
      		 
        LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
      		 
        LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
      		 
        LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
      		 
        LEFT JOIN t2 c28 ON c28.parent_id = t.id AND c28.col2 = "val"
      		 
        LEFT JOIN t2 c29 ON c29.parent_id = t.id AND c29.col2 = "val"
      		 
        LEFT JOIN t2 c30 ON c30.parent_id = t.id AND c30.col2 = "val"
      		 
        LEFT JOIN t2 c31 ON c31.parent_id = t.id AND c31.col2 = "val"
      		 
        LEFT JOIN t2 c32 ON c32.parent_id = t.id AND c32.col2 = "val"
      		 
        LEFT JOIN t2 c33 ON c33.parent_id = t.id AND c33.col2 = "val"
      		 
      ORDER BY 
        col1;
      		 
       
      		 
      select now();

      and then run it.
      First, run it with join_cache_level=0 - it will finish instantly
      Then, run it with join_cache_level=2 (the default). The query will take more than 10 minutes (more in debug builds).

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          Description A A query over empty tables is extremely slow when outer join is used together with join buffering (which is ON by default).

          Take this file
          {noformat}
          drop table if exists t2,t1;

          CREATE TABLE `t1` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `col1` varchar(255) NOT NULL DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;

          CREATE TABLE `t2` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `parent_id` smallint(3) NOT NULL DEFAULT '0',
            `col2` varchar(25) NOT NULL DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

          select now();
          SELECT t.*
          FROM
            t1 t
            LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
            LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
            LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
            LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
            LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
            LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
            LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
            LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
            LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
            LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
            LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
            LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
            LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
            LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
            LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
            LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
            LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
            LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
            LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
            LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
            LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
            LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
            LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
            LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
            LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
            LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
            LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
            LEFT JOIN t2 c28 ON c28.parent_id = t.id AND c28.col2 = "val"
            LEFT JOIN t2 c29 ON c29.parent_id = t.id AND c29.col2 = "val"
            LEFT JOIN t2 c30 ON c30.parent_id = t.id AND c30.col2 = "val"
            LEFT JOIN t2 c31 ON c31.parent_id = t.id AND c31.col2 = "val"
            LEFT JOIN t2 c32 ON c32.parent_id = t.id AND c32.col2 = "val"
            LEFT JOIN t2 c33 ON c33.parent_id = t.id AND c33.col2 = "val"
          ORDER BY
            col1;

          select now();
          {noformat}

          and then run it.
          First, run it with join_cache_level=0 - it will finish instantly
          Then, run it with join_cache_level=2 (the default). The query will take more than 10 minutes.
          psergei Sergei Petrunia made changes -
          Description A query over empty tables is extremely slow when outer join is used together with join buffering (which is ON by default).

          Take this file
          {noformat}
          drop table if exists t2,t1;

          CREATE TABLE `t1` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `col1` varchar(255) NOT NULL DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;

          CREATE TABLE `t2` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `parent_id` smallint(3) NOT NULL DEFAULT '0',
            `col2` varchar(25) NOT NULL DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

          select now();
          SELECT t.*
          FROM
            t1 t
            LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
            LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
            LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
            LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
            LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
            LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
            LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
            LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
            LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
            LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
            LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
            LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
            LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
            LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
            LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
            LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
            LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
            LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
            LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
            LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
            LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
            LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
            LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
            LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
            LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
            LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
            LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
            LEFT JOIN t2 c28 ON c28.parent_id = t.id AND c28.col2 = "val"
            LEFT JOIN t2 c29 ON c29.parent_id = t.id AND c29.col2 = "val"
            LEFT JOIN t2 c30 ON c30.parent_id = t.id AND c30.col2 = "val"
            LEFT JOIN t2 c31 ON c31.parent_id = t.id AND c31.col2 = "val"
            LEFT JOIN t2 c32 ON c32.parent_id = t.id AND c32.col2 = "val"
            LEFT JOIN t2 c33 ON c33.parent_id = t.id AND c33.col2 = "val"
          ORDER BY
            col1;

          select now();
          {noformat}

          and then run it.
          First, run it with join_cache_level=0 - it will finish instantly
          Then, run it with join_cache_level=2 (the default). The query will take more than 10 minutes.
          		 A query over empty tables is extremely slow when outer join is used together with join buffering (which is ON by default).

          Take this file
          {noformat}
          drop table if exists t2,t1;

          CREATE TABLE `t1` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `col1` varchar(255) NOT NULL DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;

          CREATE TABLE `t2` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `parent_id` smallint(3) NOT NULL DEFAULT '0',
            `col2` varchar(25) NOT NULL DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

          select now();
          SELECT t.*
          FROM
            t1 t
            LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
            LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
            LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
            LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
            LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
            LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
            LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
            LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
            LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
            LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
            LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
            LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
            LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
            LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
            LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
            LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
            LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
            LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
            LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
            LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
            LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
            LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
            LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
            LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
            LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
            LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
            LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
            LEFT JOIN t2 c28 ON c28.parent_id = t.id AND c28.col2 = "val"
            LEFT JOIN t2 c29 ON c29.parent_id = t.id AND c29.col2 = "val"
            LEFT JOIN t2 c30 ON c30.parent_id = t.id AND c30.col2 = "val"
            LEFT JOIN t2 c31 ON c31.parent_id = t.id AND c31.col2 = "val"
            LEFT JOIN t2 c32 ON c32.parent_id = t.id AND c32.col2 = "val"
            LEFT JOIN t2 c33 ON c33.parent_id = t.id AND c33.col2 = "val"
          ORDER BY
            col1;

          select now();
          {noformat}

          and then run it.
          First, run it with join_cache_level=0 - it will finish instantly
          Then, run it with join_cache_level=2 (the default). The query will take more than 10 minutes (more in debug builds).
          psergei Sergei Petrunia made changes -
          Assignee Igor Babaev [ igor ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.38 [ 15400 ]
          psergei Sergei Petrunia made changes -
          Labels upstream
          psergei Sergei Petrunia made changes -
          serg Sergei Golubchik made changes -
          Priority Major [ 3 ] Minor [ 4 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.39 [ 15800 ]
          Fix Version/s 5.5.38 [ 15400 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 41706 ] MariaDB v2 [ 43582 ]
          igor Igor Babaev (Inactive) made changes -
          Component/s Optimizer [ 10200 ]
          Fix Version/s 5.3.13 [ 12602 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.15 [ 17300 ]
          Fix Version/s 5.5.41 [ 17600 ]
          Fix Version/s 5.5 [ 15800 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 43582 ] MariaDB v3 [ 61786 ]
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0.14 [ 17101 ]
          Fix Version/s 10.0.15 [ 17300 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 61786 ] MariaDB v4 [ 147926 ]

          People

            igor Igor Babaev (Inactive)
            psergei Sergei Petrunia
            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.