-
Suggestion
-
Resolution: Fixed
-
None
Most of the commonly used queries on the SpacePermissions table are queries on the permtype, user and group columns together, but we have no composite index on these columns.
Adding one should greatly improve performance to these queries.
This change creates very little risk, since the Space Permissions table is very rarely updated.
One large system experienced improved performance when such an index was added to its oracle database.
Note that this index might be ineffective in some systems because the column values and the query values involve NULLs, which can sometimes limit the effectiveness of an index.
- is blocked by
-
- Closed
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
[CONFSERVER-14488] There should be a composite index on some of the columns in SpacePermissions table
A customer has reported that they're getting better performance from a different order, as the number of distinct values is different in the different columns:
PERMUSERNAME, PERMGROUPNAME, PERMTYPE
Per Fragemann [Atlassian]
added a comment - transitioning all public issues that are marked as fixed against internal milestones m1 to m7 to the new fixed-for version 3.0, so they show up better in the changelog. will soon archive all milestones, so these issues would not show up anymore in jira otherwise
Per Fragemann [Atlassian]
added a comment - transitioning all public issues that are marked as fixed against internal milestones m1 to m7 to the new fixed-for version 3.0, so they show up better in the changelog. will soon archive all milestones, so these issues would not show up anymore in jira otherwise The index for this is similar to:
MySQL:
CREATE INDEX SP_COMPOSITE_IDX ON SPACEPERMISSIONS (PERMTYPE, PERMUSERNAME, PERMGROUPNAME)
Oracle:
CREATE INDEX ORACONFLUENCE.SP_COMPOSITE_IDX ON ORACONFLUENCE.SPACEPERMISSIONS
(PERMTYPE, PERMUSERNAME, PERMGROUPNAME)
The hibernate library has been upgraded, so I've added the index. Hopefully this will improve the performance of some queries against this table on some databases.
This fix is a simple change to the relevant hibernate file. Well, it will be once we depend on the version of hibernate with a fix to CONF-14490 in it. (Which hibernate 3 has, but hibernate 2 doesn't have)
Hi Jeremy,
Absolutely I could see that would be a better order for databases that support it. I believe some databases (oracle) don't let you have a composite index where the first column can contain nulls, which unfortunately PERMUSERNAME can.