Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Unresolved
-
1.2.5
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
- relates to
-
MCOL-4408 Updates are slower than in 1.2.5
- Closed