[jira] [Closed] (SENTRY-2369) Remove the index for `USER_ID` and `DB_PRIVILEGE_ID` in `SENTRY_USER_DB_PRIVILEGE_MAP`

["Na Li (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SENTRY-2369?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Na Li closed SENTRY-2369.
-------------------------

> 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
>             Fix For: 2.1.0
>
>
> 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)