MariaDB Temporal Tables: Uncut & Uncensored (Part II)

There are really four dimensions, three which we call the three planes of Space, and a fourth, Time. There is, however, a tendency to draw an unreal distinction between the former three dimensions and the latter, because it happens that our consciousness moves intermittently in one direction along the latter from the beginning to the end of our lives.

H.G. Wells - The time machine

System-Versioned Tables table structure

MariaDB [pythian]> select column_name from information_schema.columns where
  -> table_name='sv_table_ts';
 +-------------+
 | column_name |
 +-------------+
 | id |
 | x |
 | text |
 +-------------+
 3 rows in set (0.001 sec)
 
 

MariaDB [pythian]> select ist.name, isc.name
  -> from
  -> information_schema.innodb_sys_columns isc
  -> inner join
  -> information_schema.innodb_sys_tables ist
  -> on ist.table_id=isc.table_id
  -> where ist.name = 'pythian/sv_table_ts';
 +---------------------+-----------+
 | name | name |
 +---------------------+-----------+
 | pythian/sv_table_ts | id |
 | pythian/sv_table_ts | x |
 | pythian/sv_table_ts | text |
 | pythian/sv_table_ts | row_start |
 | pythian/sv_table_ts | row_end |
 +---------------------+-----------+
 5 rows in set (0.001 sec)
 
 

MariaDB [pythian]> select id,x,row_start,row_end from sv_table_ts for system_time all;
 +----+------+----------------------------+----------------------------+
 | id | x | row_start | row_end |
 +----+------+----------------------------+----------------------------+
 | 1 | 1 | 2019-11-28 20:55:17.628237 | 2019-11-28 20:55:50.095010 |
 | 1 | 3 | 2019-11-28 20:55:50.095010 | 2038-01-19 03:14:07.999999 |
 | 2 | 2 | 2019-11-28 20:55:33.097465 | 2019-11-28 20:56:14.383640 |
 +----+------+----------------------------+----------------------------+
 3 rows in set (0.001 sec)
 
 

System-Versioned Tables key structure

MariaDB [pythian]> select ist.name,
  -> isi.name,
  -> isf.pos,
  -> isf.name
  -> from information_schema.innodb_sys_indexes isi
  -> inner join
  -> information_schema.innodb_sys_tables ist
  -> on ist.table_id=isi.table_id
  -> inner join
  -> information_schema.innodb_sys_fields isf
  -> on isi.index_id = isf.index_id
  -> where ist.name = 'pythian/sv_table_ts'
  -> order by isi.name, isf.pos;
 +---------------------+---------+-----+---------+
 | name | name | pos | name |
 +---------------------+---------+-----+---------+
 | pythian/sv_table_ts | PRIMARY | 0 | id |
 | pythian/sv_table_ts | PRIMARY | 1 | row_end |
 +---------------------+---------+-----+---------+
 2 rows in set (0.000 sec)
 
 

System-Versioned Tables performance considerations

MariaDB [pythian]> explain select * from sv_table_ts for system_time all\G
 *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: sv_table_ts
  type: ALL
 possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 3
  Extra:
 1 row in set (0.000 sec)
 
 MariaDB [pythian]> explain select * from sv_table_ts for system_time as of timestamp'2019-11-28 11:49:53.221854'\G
 *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: sv_table_ts
  type: ALL
 possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 3
  Extra: Using where
 1 row in set (0.000 sec)
 
 MariaDB [pythian]> explain select * from sv_table_ts for system_time all where id = 1\G
 *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: sv_table_ts
  type: ref
 possible_keys: PRIMARY
  key: PRIMARY
  key_len: 8
  ref: const
  rows: 2
  Extra:
 1 row in set (0.000 sec)
 
 MariaDB [pythian]> explain select * from sv_table_ts for system_time as of timestamp'2019-11-28 11:49:53.221854' where id = 1\G
 *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: sv_table_ts
  type: range
 possible_keys: PRIMARY
  key: PRIMARY
  key_len: 15
  ref: NULL
  rows: 1
  Extra: Using where
 1 row in set (0.000 sec)
 
 

MariaDB [pythian]> alter table sv_table_ts add index sv_index(row_start,row_end);
 Query OK, 0 rows affected (0.016 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 MariaDB [pythian]> explain select * from sv_table_ts for system_time as of timestamp'2019-11-28 11:49:53.221854'\G
 *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: sv_table_ts
  type: range
 possible_keys: sv_index
  key: sv_index
  key_len: 7
  ref: NULL
  rows: 3
  Extra: Using index condition
 1 row in set (0.000 sec)