You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@sentry.apache.org by "Na Li (JIRA)" <ji...@apache.org> on 2018/08/28 04:49:00 UTC
[jira] [Updated] (SENTRY-2369) Remove the index for `USER_ID` and
`DB_PRIVILEGE_ID` in `SENTRY_USER_DB_PRIVILEGE_MAP`
[ https://issues.apache.org/jira/browse/SENTRY-2369?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Na Li updated SENTRY-2369:
--------------------------
Summary: Remove the index for `USER_ID` and `DB_PRIVILEGE_ID` in `SENTRY_USER_DB_PRIVILEGE_MAP` (was: Remove the index for )
> 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)