Create index on the TBL_COL_PRIVS table for the columns COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID

In one of the escalations for HDP-3.1.0 we found that the table privilege checks could be very slow and these checks could be speed up by defining an INDEX on the TBL_COL_PRIVS table for the following columns: COLUMN_NAME,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_ID

In the MYSQL slow query log, we found that the following query is executed slowly:

SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege' AS `NUCLEUS_TYPE`,`A0`.`AUTHORIZER`,`A0`.`COLUMN_NAME`,`A0`.`CREATE_TIME`,`A0`.`GRANT_OPTION`,`A0`.`GRANTOR`,`A0`.`GRANTOR_TYPE`,`A0`.`PRINCIPAL_NAME`,`A0`.`PRINCIPAL_TYPE`,`A0`.`TBL_COL_PRIV`,`A0`.`TBL_COLUMN_GRANT_ID` FROM `TBL_COL_PRIVS` `A0` LEFT OUTER JOIN `TBLS` `B0` ON `A0`.`TBL_ID` = `B0`.`TBL_ID` LEFT OUTER JOIN `DBS` `C0` ON `B0`.`DB_ID` = `C0`.`DB_ID` WHERE `A0`.`PRINCIPAL_NAME` = 'xxx' AND `A0`.`PRINCIPAL_TYPE` = 'GROUP' AND `B0`.`TBL_NAME` = 'xxxx' AND `C0`.`NAME` = 'xxx' AND `C0`.`CTLG_NAME` = 'xxx' AND `A0`.`COLUMN_NAME` = 'xxx'

When checked the explain plan of the this query, it could be seen that the index defined on the TBL_COL_PRIVS table is not used. In the slow query, the COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID columns were used, and after creating an index on these columns only, we saw significant performance improvement.