[jira] [Created] (SENTRY-2369) Remove the index for

[Na Li (JIRA)]

Na Li created SENTRY-2369:
-----------------------------

             Summary: Remove the index for 
                 Key: SENTRY-2369
                 URL: https://issues.apache.org/jira/browse/SENTRY-2369
             Project: Sentry
          Issue Type: Bug
            Reporter: Na Li
            Assignee: Na Li


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)