[
https://issues.apache.org/jira/browse/SENTRY-2369?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Na Li resolved SENTRY-2369.
---------------------------
Resolution: Not A Problem
Talked more with others
We don't need to add index for USER_ID, but having more index does not have
more overhead. And we need to add the index for DB_PRIVILEGE_ID just because
the composite primary key and it only has the second level index for being part
of the primary key.
Therefore, we don't remove the index.
> Remove the index for `USER_ID` and `DB_PRIVILEGE_ID` in
> `SENTRY_USER_DB_PRIVILEGE_MAP`
> --------------------------------------------------------------------------------------
>
> Key: SENTRY-2369
> URL: https://issues.apache.org/jira/browse/SENTRY-2369
> Project: Sentry
> Issue Type: Bug
> Reporter: Na Li
> Assignee: Na Li
> Priority: Major
>
> Sergio pointed out that we should not create index for `USER_ID` and
> `DB_PRIVILEGE_ID` in `SENTRY_USER_DB_PRIVILEGE_MAP` explicitly because they
> are used as primary key. Database will automatically create index for them.
> Adding index explicitly for them only increase overhead, but does not add
> benefit.
> Need to verify that the above statement applies all supported DB
> {code}
> -- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship
> CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` (
> `USER_ID` BIGINT NOT NULL,
> `DB_PRIVILEGE_ID` BIGINT NOT NULL,
> `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP`
> ADD CONSTRAINT `SENTRY_USR_DB_PRV_MAP_PK` PRIMARY KEY
> (`USER_ID`,`DB_PRIVILEGE_ID`);
> ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP`
> ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK`
> FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER`(`USER_ID`);
> ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP`
> ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_DB_PRV_FK`
> FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES
> `SENTRY_DB_PRIVILEGE`(`DB_PRIVILEGE_ID`);
> CREATE INDEX `SEN_USR_DB_PRV_MAP_USR_FK_IDX` ON
> `SENTRY_USER_DB_PRIVILEGE_MAP` (`USER_ID`);
> CREATE INDEX `SEN_USR_DB_PRV_MAP_PRV_FK_IDX` ON
> `SENTRY_USER_DB_PRIVILEGE_MAP` (`DB_PRIVILEGE_ID`);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
