Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3439

Very slow delete/update in mariadb (columnstore instance) using primary key

    XMLWordPrintable

Details

    Description

      We have table with InnoDB engine that have 5 million rows and primary key of 2 columns. DDL:

      CREATE TABLE `interest` (
      `src_id` TINYINT NOT NULL,
      `id` binary(36) NOT NULL,
      `account_id` bigint(20) NOT NULL,
      `instrument_id` int(11) NOT NULL,
      `quantity` decimal(15,4) NOT NULL,
      `time` datetime NOT NULL,
      `interest` decimal(16,2) NOT NULL,
      `auto_incr` bigint(20) NOT NULL ,
      PRIMARY KEY (`src_id`, `id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      and both of these queries takes 4-5 seconds to execute:

      DELETE FROM interest WHERE `id`='52f35ddb-94d0-4744-bb3c-f3ae8100dc8e' AND `src_id`='1';

      UPDATE interest SET interest = 1 WHERE `id`='52f35ddb-94d0-4744-bb3c-f3ae8100dc8e' AND `src_id`='1';

      I have run EXPLAIN on this query and it says that the query will be executed over 3.5 milion rows using primary key. It looks like that primary key doesn't take effect because it scans more than 50% of the rows.

      I exported the data from this table and import it in table within normal mariadb server instance and there is no such problem. Both query execute under 10ms.

      We are using latest version of MariaDB column store: 1.2.5

      I also have stackoverflow question where you can see updates: https://stackoverflow.com/questions/57559471/very-slow-delete-update-in-mariadb-columnstore-server-using-primary-key

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              staykov Georgi Staykov
              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.