Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.3(EOL), 10.4(EOL)
-
None
Description
row_end is not used when searching by PRIMARY KEY
create table t (pk int primary key) with system versioning; |
insert into t values (1), (2); |
explain extended delete from t where pk=1; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY PRIMARY 4 NULL 1 100.00 Using where |
explain extended update t set pk=11 where pk=1; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using buffer |
Both queries should affect only current system rows. In both queries PK is used which consists of (pk, row_end). row_end = current_timestamp is implied and it would be useful to use second keypart for filtering. But EXPLAIN shows only 4 bytes of key is used which means pk only.
Using row_end will not change correctness. But it can speed up some queries a bit.
Attachments
Issue Links
- duplicates
-
MDEV-18727
System Versioning: optimize DML operation
-
- Closed
-
Activity
> row_end is not expanded to WHERE. Historical rows are filtered like this: https://github.com/MariaDB/server/blob/bbf780efcd26f468ec83ede5ecc18ca6f96802fb/sql/sql_delete.cc#L232
In SELECT it is. Why not to use this logic everywhere? It'd make sense, instead of having DELETE-specific post-filtering in sql_delete.cc
This issue was created to make exactly what you'd say. UPDATE also can be improved.
kevg, are you going to do it yourself, or does it need to be assigned to somebody in MariaDB?
Fuller test case to check if READ_RECORD correctly uses both fields of index:
--source include/have_sequence.inc
|
create table t (pk int primary key, z int) with system versioning; |
insert into t values (301, 0); |
update t set z= z + 1; |
update t set z= z + 1; |
update t set z= z + 1; |
update t set z= z + 1; |
delete from t; |
insert into t select seq, 0 from seq_0_to_1000; |
explain extended delete from t where pk = 1; |
explain extended update t set pk=11 where pk = 1; |
explain extended select * from t where pk = 300; |
delete from t where pk < 200; |
update t set z= 1 where pk = 301; |
delete from t where pk = 301; |
drop table t; |
Since MDEV-18727 it works correctly utilizing both fields of index.
explain extended delete from t where pk = 1; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY PRIMARY 11 NULL 1 100.00 Using where |
explain extended update t set pk=11 where pk = 1; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY PRIMARY 11 NULL 1 100.00 Using where |
Why does it not happen automatically? The WHERE clause is expanded to include a condition on row_end. Optimizer should notice that all key parts of a primary key are defined and use the eq_ref.