[
https://issues.apache.org/jira/browse/SENTRY-2210?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16456708#comment-16456708
]
Na Li commented on SENTRY-2210:
-------------------------------
Kalyan raised a concern that MySql automatically adds an index for foreign key.
When we ad index on the foreign key explicitly, will there be two index for the
same foreign key and cause extra overhead?
1)The test shows that after defining foreign key, MySql does automatically
added the key fro the foreign key. *KEY `AUTHZ_PATH_FK` (`AUTHZ_OBJ_ID`),*
{code}
MariaDB [testSentry]> show create table AUTHZ_PATH;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| AUTHZ_PATH | CREATE TABLE `AUTHZ_PATH` (
`PATH_ID` bigint(20) NOT NULL,
`PATH_NAME` varchar(4000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`AUTHZ_OBJ_ID` bigint(20) DEFAULT NULL,
PRIMARY KEY (`PATH_ID`),
KEY `AUTHZ_PATH_FK` (`AUTHZ_OBJ_ID`),
CONSTRAINT `AUTHZ_PATH_FK` FOREIGN KEY (`AUTHZ_OBJ_ID`) REFERENCES
`AUTHZ_PATHS_MAPPING` (`AUTHZ_OBJ_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}
2) After adding index for the foreign key explicitly, the automatically created
key is removed. So there is no more than one index on the same foreign key.
*KEY `AUTHZ_PATH_FK_IDX` (`AUTHZ_OBJ_ID`),*
{code}
MariaDB [testSentry]> CREATE INDEX `AUTHZ_PATH_FK_IDX` ON `AUTHZ_PATH`
(`AUTHZ_OBJ_ID`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testSentry]> show create table AUTHZ_PATH;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| AUTHZ_PATH | CREATE TABLE `AUTHZ_PATH` (
`PATH_ID` bigint(20) NOT NULL,
`PATH_NAME` varchar(4000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`AUTHZ_OBJ_ID` bigint(20) DEFAULT NULL,
PRIMARY KEY (`PATH_ID`),
KEY `AUTHZ_PATH_FK_IDX` (`AUTHZ_OBJ_ID`),
CONSTRAINT `AUTHZ_PATH_FK` FOREIGN KEY (`AUTHZ_OBJ_ID`) REFERENCES
`AUTHZ_PATHS_MAPPING` (`AUTHZ_OBJ_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{code}
> AUTHZ_PATH should have index on the foreign key AUTHZ_OBJ_ID
> ------------------------------------------------------------
>
> Key: SENTRY-2210
> URL: https://issues.apache.org/jira/browse/SENTRY-2210
> Project: Sentry
> Issue Type: Improvement
> Components: Sentry
> Affects Versions: 2.1.0
> Reporter: Na Li
> Assignee: Na Li
> Priority: Major
> Attachments: SENTRY-2210.001.patch, SENTRY-2210.002.patch,
> SENTRY-2210.003.patch, SENTRY-2210.004.patch
>
>
> AUTHZ_PATH does not have index on the foreign key AUTHZ_OBJ_ID. When the
> number of entries become really large, a command like "select * from
> AUTHZ_PATH where AUTHZ_OBJ_ID = 8311512" becomes very slow because it results
> in table scan.
> To improve performance, we should add index for its foreign key in table
> AUTHZ_PATH.
> The sql script to do this in Oracle is "CREATE INDEX AUTHZ_PATH_FK_IDX on
> AUTHZ_PATH(AUTHZ_OBJ_ID);"
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
