[
https://issues.apache.org/jira/browse/HIVE-26144?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17523142#comment-17523142
]
Janos Kovacs commented on HIVE-26144:
-------------------------------------
For the WLN_ID: notification cleaner is through JDO, deleting rows with older
TTL on WLN_ID. Without the index the delete used full table scans, with index
the delete is direct.
{noformat}
MariaDB [(none)]> explain DELETE FROM hivedb.TXN_WRITE_NOTIFICATION_LOG WHERE
`WNL_ID`=880867;
+------+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+------+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | TXN_WRITE_NOTIFICATION_LOG | ALL | NULL | NULL |
NULL | NULL | 1 | Using where |
+------+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [(none)]> create index ind_WNL_ID on
hivedb.TXN_WRITE_NOTIFICATION_LOG(WNL_ID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [(none)]> explain DELETE FROM hivedb.TXN_WRITE_NOTIFICATION_LOG WHERE
`WNL_ID`=880867;
+------+-------------+----------------------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+------+-------------+----------------------------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | TXN_WRITE_NOTIFICATION_LOG | range | ind_WNL_ID |
ind_WNL_ID | 8 | NULL | 1 | Using where |
+------+-------------+----------------------------+-------+---------------+------------+---------+------+------+-------------+
{noformat}
The others are from older cases, let me find the details.
> Add keys/indexes to support highly concurrent workload
> ------------------------------------------------------
>
> Key: HIVE-26144
> URL: https://issues.apache.org/jira/browse/HIVE-26144
> Project: Hive
> Issue Type: Sub-task
> Components: Database/Schema
> Reporter: Janos Kovacs
> Assignee: Janos Kovacs
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following indexes are added to avoid full table-scan in backend rdbms:
> - primary key for COMPLETED_TXN_COMPONENTS
> - primary key for TXN_COMPONENTS
> - index for TXN_WRITE_NOTIFICATION_LOG
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
