[MDEV-15842] Index is not used for row_end in UPDATE/DELETE Created: 2018-04-10  Updated: 2022-03-26  Resolved: 2022-03-26

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Eugene Kosov (Inactive) Assignee: Aleksey Midenkov
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-18727 System Versioning: optimize DML opera... Closed

 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.

 Comments   
Comment by Sergei Golubchik [ 2018-07-09 ]

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.

Comment by Eugene Kosov (Inactive) [ 2018-07-09 ]

> 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

Comment by Sergei Golubchik [ 2018-07-09 ]

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

Comment by Eugene Kosov (Inactive) [ 2018-07-09 ]

This issue was created to make exactly what you'd say. UPDATE also can be improved.

Comment by Elena Stepanova [ 2018-08-06 ]

kevg, are you going to do it yourself, or does it need to be assigned to somebody in MariaDB?

Comment by Eugene Kosov (Inactive) [ 2018-08-07 ]

I will do it by myself.

Comment by Aleksey Midenkov [ 2022-03-26 ]

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

Generated at Thu Feb 08 08:24:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.