[SENTRY-2369] Remove the index for `USER_ID` and `DB_PRIVILEGE_ID` in `SENTRY_USER_DB_PRIVILEGE_MAP`

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Fix Version/s: 2.1.0
• Assignee: Na Li
• Reporter: Na Li
• Created: 28/Aug/18 04:47
• Updated: 28/Aug/18 21:13
• Resolved: 28/Aug/18 21:13

Description

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

-- 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`);