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

Optimizer trace: lots of join_execution elements produced for correlated subquery

    XMLWordPrintable

Details

    Description

      create table ten(a int primary key);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int primary key);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      explain
      select * from one_k where a > 1000 + (select max(a) from ten where ten.a<one_k.a);
      

      +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | PRIMARY            | one_k | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
      |    2 | DEPENDENT SUBQUERY | ten   | ALL  | NULL          | NULL | NULL    | NULL | 10   | Using where |
      +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
      

      Now, run this:

      set optimizer_trace=1;
      select * from one_k where a > 1000 + (select max(a) from ten where ten.a<one_k.a);
      select * from information_schema.optimizer_trace\G
      

      And see lots of these in the trace:

                  "join_execution": {
                    "select_id": 2,
                    "steps": []
                  }
                  "join_execution": {
                    "select_id": 2,
                    "steps": []
                  }
                  "join_execution": {
                    "select_id": 2,
                    "steps": []
                  }
                  "join_execution": {
                    "select_id": 2,
                    "steps": []
                  }
      

      Do they have any value?

      Attachments

        Activity

          People

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