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

Single-table UPDATE/DELETE: make cost-based choice between subquery strategies

    XMLWordPrintable

Details

    Description

      There is a query in my application to delete duplicate records (specific fields), this query used to work fine on MySQL (v 8.0.21) on Windows 10 and takes about a minute,

      When I migrated to MariaDB 10.5.9 on Ubuntu 20.04 , this query stucks, I left it for +6 hours and it didn't come back.

      The table has about 4 million records, table is:

      CREATE TABLE `item_variant_price` (
      		 
        `seller_variant_id` int(11) NOT NULL AUTO_INCREMENT,
      		 
        `item_id` varchar(100) DEFAULT NULL,
      		 
        `price` float DEFAULT NULL,
      		 
        `seller_name` varchar(400) DEFAULT NULL,
      		 
        `variant` varchar(400) DEFAULT NULL,
      		 
        `is_fulfilled` int(11) NOT NULL DEFAULT 0,
      		 
        `insertion_date` timestamp NOT NULL DEFAULT current_timestamp(),
      		 
        `modification_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      		 
        `is_main_page` int(11) DEFAULT 0,
      		 
        `is_cheapest` int(11) NOT NULL DEFAULT 0,
      		 
        `variant_url` varchar(400) DEFAULT NULL,
      		 
        PRIMARY KEY (`variant_id`),
      		 
        FULLTEXT KEY `item_variant_price_serial_IDX` (`item_id`,`seller_name`,`variant`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=4309337 DEFAULT CHARSET=utf8mb4;

      and this is the query:

      delete from item_variant_price where seller_variant_id not in (select m from (select max(seller_variant_id) m from item_variant_price group by item_id, seller_name, variant) as innerTable);

      the inner select statement takes 27 seconds, so there has to be an issue with delete ?

      Thanks for your support

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35318 Assertion `tl->jtbm_subselect' failed in JOIN::calc_allowed_top_level_tables on 2nd execution of PS

          • Blocker - Blocks development and/or testing work, production could not run.
          • In Review

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35662 Assertion failure in diagnostics area upon EXPLAIN UPDATE

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          duplicates

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22415 Single table UPDATE/DELETE doesn't use non-semijoin Materialization

          • Major - Major loss of function.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22415 Single table UPDATE/DELETE doesn't use non-semijoin Materialization

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35231 Wrong EXPLAIN or ANALYZE output: SUBQUERY instead of DEPENDENT SUBQUERY

          • Major - Major loss of function.
          • Open

          Activity

            People

              ycp Yuchen Pei
              ahmedadel47 Ahmed Wahba
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.