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
| Field | Original Value | New Value |
|---|---|---|
| Issue Type | Task [ 3 ] | Bug [ 1 ] |
| Affects Version/s | 10.3 [ 22126 ] |
| Summary | Use row_end when search by PRIMARY KEY | row_end is not used when searching by PRIMARY KEY |
| Labels | need_feedback |
> 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.
| Labels | need_feedback |
kevg, are you going to do it yourself, or does it need to be assigned to somebody in MariaDB?
| Labels | need_feedback |
| Assignee | Eugene Kosov [ kevg ] |
| Labels | need_feedback |
| Affects Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Workflow | MariaDB v3 [ 86475 ] | MariaDB v4 [ 140725 ] |
| Assignee | Eugene Kosov [ kevg ] | Aleksey Midenkov [ midenok ] |
| Description |
{code:sql}
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 {code} 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. |
row_end is not used when searching by PRIMARY KEY
{code:sql} 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 {code} 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. |
| Summary | row_end is not used when searching by PRIMARY KEY | Index is not used for row_end in UPDATE/DELETE |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Link |
This issue duplicates |
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 |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Duplicate [ 3 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
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.