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

Semi-Join optimization for single-table update

    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

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              matzehh Matthias Schröder
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: