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

Semi-Join optimization for single-table update

    XMLWordPrintable

Details

    Description

      It seems better to switch to multi_table_update code if subqueries are involved in order to gain from semi-join optimization:

      Before

      MariaDB [test_real]> explain UPDATE akte set ckunr = concat('X',trim(ckunr)) where naktnr IN (SELECT naktnr FROM imp_adr where cdiskid='D_0000010561518');

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY akte ALL NULL NULL NULL NULL 8559040 Using where
      2 DEPENDENT SUBQUERY imp_adr ref naktnr,cdiskid cdiskid 15 const 328 Using where with pushed condition
      After

      MariaDB [myads]> explain UPDATE akte set ckunr = concat('X',trim(ckunr)) where naktnr IN (SELECT naktnr FROM imp_adr where cdiskid='D_0000010561518');

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 328  
      1 PRIMARY akte eq_ref naktnr naktnr 4 myads.imp_adr.naktnr 1  
      2 MATERIALIZED imp_adr ref naktnr,cdiskid cdiskid 15 const 328 Using where with pushed condition

      I am on the mariadb-server 10.3 branch, latest code.

      Attachments

        1. patch.diff
          1 kB
          Matthias Schröder

        Issue Links

          Activity

            People

              igor Igor Babaev
              matzehh Matthias Schröder
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.