DELETE only needs row id, partition key and indexed columns
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```sql
mysql> create table t (a varchar(100), b int, c int, primary key(a, b) nonclustered);
Query OK, 0 rows affected (0.17 sec)
mysql> explain delete from t where a in ('aaaaa', 'b');
+----------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------+
| Delete_4 | N/A | root | | N/A |
| └─IndexLookUp_11 | 20.00 | root | | |
| ├─IndexRangeScan_9(Build) | 20.00 | cop[tikv] | table:t, index:PRIMARY(a, b) | range:["aaaaa","aaaaa"], ["b","b"], keep order:false, stats:pseudo |
| └─TableRowIDScan_10(Probe) | 20.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------+
4 rows in set (0.01 sec)
```
For the delete operation, only `_tidb_rowid` and the value `a` and `b` are required. In this case, `IndexLookUp` can be eliminated to a `IndexRangeScan`.
### Detailed work items
We split the cases into details:
- [x] The most simple case: single table delete on a normal table(no foreign key, no partition)
- [x] Multi-table delete
- [ ] Support for tables containing foreign key
- [ ] Support for tables containing partitions
A DELETE will contain the read path, reading the needed rows from storage. And the write path, deleting the given row and its indexes. To save the network cost, we need to support the column pruning both for the read path and the write path:
- Read path can only read needed columns
- [x] Read by coprocessor (by default)
- [ ] Read by Get/BatchGet
- A more complicated case: read path can only read needed field from a large column(JSON, TEXT)
- [x] Read by coprocessor (by projection push down)
- [ ] Read by Get/BatchGet
- [ ] Write path can delete the record by the given column, not the full row
- [x] normal table
- #54009
- #54010
- #54065
- #56077
- [ ] partitioned table
- [ ] foreign keys
To support the most simple and common case, we need to modify the write path and let planner change the column reference map.
Contributor guide
Assessment
This issue has not been assessed yet.