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

UPDATE on multiple tables won't use the hashed join

    XMLWordPrintable

Details

    Description

      System settings:
      join_cache_level = 8
      optimizer_switch join_cache_hashed=on

      Tables definition:

      CREATE TABLE IMPTest1 (id INT NOT NULL, is_ready INT NOT NULL)
      ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
       
      CREATE TABLE IMPTest2 (id INT NOT NULL, is_ready INT NOT NULL)
      ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
      

      Queries:

      EXPLAIN FORMAT=JSON
      SELECT * FROM IMPTest1 INNER JOIN IMPTest2 ON IMPTest1.id = IMPTest2.id;
      

      Result:

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "IMPTest2",
            "access_type": "ALL",
            "rows": 8,
            "filtered": 100
          },
          "block-nl-join": {
            "table": {
              "table_name": "IMPTest1",
              "access_type": "hash_ALL",
              "key": "#hash#$hj",
              "key_length": "4",
              "used_key_parts": ["id"],
              "ref": ["kav_ilin.IMPTest2.id"],
              "rows": 16,
              "filtered": 100
            },
            "buffer_type": "flat",
            "buffer_size": "100Mb",
            "join_type": "BNLH",
            "attached_condition": "imptest1.`id` = imptest2.`id`"
          }
        }
      }
      

      Update query

      EXPLAIN FORMAT=JSON
      UPDATE IMPTest1, IMPTest2 SET IMPtest1.is_ready = 1
      WHERE IMPTest1.id = IMPTest2.id;
      

      Result:

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "IMPTest2",
            "access_type": "ALL",
            "rows": 8,
            "filtered": 100
          },
          "table": {
            "table_name": "IMPTest1",
            "access_type": "ALL",
            "rows": 16,
            "filtered": 100,
            "attached_condition": "imptest1.`id` = imptest2.`id`"
          }
        }
      }
      

      I expected that the database engine would use hashed join on running the second query.

      Attachments

        Activity

          People

            Unassigned Unassigned
            Mike Ilin Mike Ilin
            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.