Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Implicit system versioning (with implicit system fields) is almost unusable because versioned queries doe full table scan and there is no way to index them. F.ex. this:
create table t1(x int primary key) with system versioning; |
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'; |
drop table t1; |
results in
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 ALL NULL NULL NULL NULL 6 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 ALL NULL NULL NULL NULL 6 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 ALL NULL NULL NULL NULL 6 Using where |
And when we add indexes:
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; |
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'; |
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 |
So adding default indexes by (row_start) and (row_end) actually is a good thing for any system versioned tables. While they are easily added in the second example it is may be not evident to a user, so probably CREATE TABLE should print a warning when no such indexes specified or maybe it should add them implicitly in any case.