[MDEV-27221] DELETE HISTORY ignores indices Created: 2021-12-10  Updated: 2022-01-24  Resolved: 2022-01-10

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3.28, 10.6.4
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Richard Stracke Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

To reproduce:

CREATE TABLE t2 (x INT,
 
`start_time` timestamp(6) GENERATED ALWAYS AS ROW START,
 
`end_time` timestamp(6) GENERATED ALWAYS AS ROW END,
 
   PERIOD FOR SYSTEM_TIME (`start_time`, `end_time`)
 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING
 
  PARTITION BY SYSTEM_TIME 
 (PARTITION `p_hist` HISTORY ENGINE = InnoDB,
 
  PARTITION `p_cur` CURRENT ENGINE = InnoDB);
 
 
 
 
 
 
 
ALTER TABLE t2 ADD INDEX `startendtime` (`start_time`, `end_time`);

Select use the index

MariaDB [test]> explain SELECT * FROM t2 FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';
 
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t2    | range | startendtime  | startendtime | 7       | NULL | 2    | Using where |
 
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
 
1 row in set (0.002 sec)

Delete apparently not

MariaDB [test]> explain DELETE HISTORY FROM t2 BEFORE SYSTEM_TIME '2016-10-09 08:07:06';
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Aleksey Midenkov [ 2021-12-16 ]

DELETE HISTORY deletes by (end_time) and there is no such index in above example. You must have both indexes by each of system fields to benefit from indexed historical queries:

create table t1(
 
  x int primary key,
 
  row_start timestamp(6) as row start invisible,
 
  row_end timestamp(6) as row end invisible,
 
  index (row_start),
 
  index (row_end),
 
  period for system_time (row_start, row_end)
 
) with system versioning;
 
insert into t1 values (1), (2), (3), (5), (6), (7);
 
explain select * from t1 for system_time as of '2021-01-01 00:00:00';
 
explain select * from t1 for system_time from '2020-01-01 00:00:00' to '2021-01-01 00:00:00';
 
explain select * from t1 for system_time between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';
 
explain delete history from t1 before system_time '2021-10-09 08:07:06';
 
drop table t1;


explain select * from t1 for system_time as of '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_start,row_end       row_start       7       NULL    1       Using index condition; Using where
 
explain select * from t1 for system_time from '2020-01-01 00:00:00' to '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_start,row_end       row_start       7       NULL    1       Using index condition; Using where
 
explain select * from t1 for system_time between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_start,row_end       row_start       7       NULL    1       Using index condition; Using where
 
explain delete history from t1 before system_time '2021-10-09 08:07:06';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_end row_end 7       NULL    1       Using where

Generated at Thu Feb 08 09:51:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.