Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27288

Add implicit indexes by row_start and row_end for system versioning

Details

    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

          No workflow transitions have been executed yet.

          People

            midenok Aleksey Midenkov
            midenok Aleksey Midenkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.