-
Type:
Bug
-
Resolution: Unresolved
-
Priority:
Medium
-
None
-
Affects Version/s: 7.19.0, 9.2.26
-
Component/s: User - Profile
-
None
-
2
-
Severity 3 - Minor
-
41
Issue Summary
On a Confluence instance with a large number of rows in SPACEPERMISSIONS (2m+) and SPACES, the user profile settings screen can take 30s - 60s to load.
The performance can worsen to 5 minute response times on /users/viewmysettings.action as more users simultaneously access the user profile settings screen.
Steps to Reproduce
Set up a Confluence instance with this data size:
Table Row Count --------------------------- spaces 8K spacepermissions 2.2M
- Flush the Cache on Confluence Cache Management screen
- Use a non-administrator user and load the User Profiles » Settings screen (i.e. users/viewmysettings.action)
Expected Results
User Profiles » Settings screen should load quickly.
Backend shouldn't run Viewable Spaces SQL unless really needed (e.g. load it on Edit User Profile settings as it's needed for selecting Site Homepage)
When it does need to run the SQL, the SQL should run quickly.
Actual Results
User Profiles » Settings screen takes a long time to load 
(performance worsens as more users use the system)
Here's a sample data collection:
HAR:
Time since last request Start Request Total Time Response Size Server HTTP Response X-AUSERNAME URL 1] 0s 2026-11-05T12:31:42.995Z 12551 ms 208.04 KB 200 person13090 http://localhost:8090/users/viewmysettings.action
Stuck Thread
..
: 20-Oct-2026 14:38:37: 12 - http-nio-8090-exec-171 3m 17s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:38:27: 11 - http-nio-8090-exec-135 3m 56s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:38:07: 11 - http-nio-8090-exec-224 4m 8s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:37:26: 11 - http-nio-8090-exec-40 4m 29s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:37:15: 10 - http-nio-8090-exec-61 4m 34s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:36:54: 11 - http-nio-8090-exec-43 3m 22s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:36:54: 10 - http-nio-8090-exec-132 4m 5s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:36:54: 9 - http-nio-8090-exec-64 4m 8s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:36:34: 9 - http-nio-8090-exec-226 3m 20s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:36:14: 8 - http-nio-8090-exec-166 3m 30s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:35:33: 9 - http-nio-8090-exec-34 5m 10s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:35:23: 8 - http-nio-8090-exec-38 4m 39s http://localhost:8090/users/viewmysettings.action
: 20-Oct-2026 14:34:53: 9 - http-nio-8090-exec-16 4m 27s http://localhost:8090/users/viewmysettings.action
..
SQL profiling logs enabled:
Time since last line Cumulative Interval Time Log lines .. 16] 0s 0s 2026-11-05 12:31:42,643 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select count(distinct space1_.SPACEID) as col_0_0_ from SPACEPERMISSIONS spacepermi0_ inner join SPACES space1... 17] 4s 0s 2026-11-05 12:31:46,443 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 18] 0s 4s 2026-11-05 12:31:46,918 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 19] 1s 4s 2026-11-05 12:31:47,305 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 20] 0s 5s 2026-11-05 12:31:47,895 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 21] 1s 5s 2026-11-05 12:31:48,342 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 22] 0s 6s 2026-11-05 12:31:48,835 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 23] 1s 6s 2026-11-05 12:31:49,261 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 24] 0s 7s 2026-11-05 12:31:49,736 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 25] 1s 7s 2026-11-05 12:31:50,193 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 26] 0s 8s 2026-11-05 12:31:50,615 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 27] 1s 8s 2026-11-05 12:31:51,022 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 28] 0s 9s 2026-11-05 12:31:51,512 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 29] 0s 9s 2026-11-05 12:31:51,892 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 30] 1s 9s 2026-11-05 12:31:52,362 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 31] 0s 10s 2026-11-05 12:31:52,873 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 32] 1s 10s 2026-11-05 12:31:53,378 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 33] 0s 11s 2026-11-05 12:31:53,770 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as space... 34] 1s 11s 2026-11-05 12:31:54,096 DEBUG [http-nio-29224-exec-7 url: /users/viewmysettings.action; user: person13090] [org.hibernate.SQL] logStatement select bucketprop0_.entity_name as entity_n1_65_0_, bucketprop0_.entity_id as entity_i2_65_0_, bucketprop0_.en...
There are two slow SQL, one with a select COUNT() and another with select DISTINCT(), both have the same WHERE clause.
select count(distinct space1_.SPACEID) as col_0_0_
from SPACEPERMISSIONS spacepermi0_ inner join SPACES space1_ on spacepermi0_.SPACEID=space1_.SPACEID
where (spacepermi0_.PERMUSERNAME='....<redacted>...' or spacepermi0_.PERMGROUPNAME in ('confluence-users') or spacepermi0_.PERMALLUSERSSUBJECT='authenticated-users' or (spacepermi0_.PERMUSERNAME is null) and (spacepermi0_.PERMGROUPNAME is null) and (spacepermi0_.PERMALLUSERSSUBJECT is null)) and spacepermi0_.PERMTYPE='VIEWSPACE' and space1_.SPACETYPE='global'
The second query is an iteration over the paginated results (500 at at time):
select distinct space1_.SPACEID as spaceid1_73_, space1_.SPACENAME as spacenam2_73_, space1_.SPACEKEY as spacekey3_73_, space1_.LOWERSPACEKEY as lowerspa4_73_, space1_.SPACEDESCID as spacedes5_73_, space1_.HOMEPAGE as homepage6_73_, space1_.CREATOR as creator7_73_, space1_.CREATIONDATE as creation8_73_, space1_.LASTMODIFIER as lastmodi9_73_, space1_.LASTMODDATE as lastmod10_73_, space1_.SPACETYPE as spacety11_73_, space1_.SPACESTATUS as spacest12_73_
from SPACEPERMISSIONS spacepermi0_ inner join SPACES space1_ on spacepermi0_.SPACEID=space1_.SPACEID
where (spacepermi0_.PERMUSERNAME='<redacted>' or spacepermi0_.PERMGROUPNAME in ('confluence-users') or spacepermi0_.PERMALLUSERSSUBJECT='authenticated-users' or (spacepermi0_.PERMUSERNAME is null) and (spacepermi0_.PERMGROUPNAME is null) and (spacepermi0_.PERMALLUSERSSUBJECT is null)) and spacepermi0_.PERMTYPE='VIEWSPACE' and space1_.SPACETYPE='global' order by space1_.SPACENAME, space1_.SPACEKEY limit 500, 501
The SQL do not greatly utilise the index, e.g.
The sp_permtype_idx index on PERMTYPE alone is nearly useless — 'VIEWSPACE' matches 268,959 out of ~548K rows (~49% of the table). MySQL is essentially doing a half-table scan.
The WHERE clause has multiple OR conditions across different columns (PERMUSERNAME, PERMGROUPNAME, PERMALLUSERSSUBJECT, plus the triple-NULL check). This prevents MySQL from using a single composite index efficiently.
DISTINCT + ORDER BY + OFFSET Pagination
The combination of DISTINCT, ORDER BY, and a deep offset (LIMIT 2500, 501) forces MySQL to:
- Deduplicate into a temporary table (8,084 rows)
- Sort all results
- Skip 2,500 rows before returning 501
Workaround
Attempt to reindex the following tables that may improve the slow SQL query performance:
- SPACEPERMISSIONS
- SPACES