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.
Attachments
Activity
What is the sence in implicit versioning then? The original sence was to make life easier, but now it turns out that implicit versioning is usable only for tests and study examples.
The assumption is that users who use system versioning a lot also can use the explicit standard syntax.
And that the implicit syntax is for users who want to track the history of the table but otherwise don't want to change the existing application, they rarely query history and more concerned about having as little performance degradation as possible.
I'm not sure it's a good idea to create indexes by default. I'd rather have users create them explicitly.
For that I suggested a feature to convert implicit to explicit system versioning. With
we could make it to convert the versioning to explicit, while preserving all historical information. Columns can be declared invisible too, that's as the user want.