Poor performance in Roles page with millions of assigned roles

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.1.X, 7.2.X, 7.3.X, Master
• Fix Version/s: 7.2.10 DXP FP10, 7.2.10.4 DXP SP4, 7.2.X, 7.3.10 DXP FP1, 7.3.10.1 DXP SP1, 7.3.6 CE GA7, 7.3.X, 7.4.13 DXP GA1, Master
• Component/s: Performance, User Management > Roles
• Labels:
  • ee-ts
  • liferay-dxp-72-sp4
  • liferay-dxp-73-sp1
  • liferay-fixpack-dxp-1-7310
  • liferay-fixpack-dxp-10-7210
• Branch Version/s:
  7.3.x, 7.2.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Sprint: U&SM| S04 Sprint 4|11.24-12.15
• Git Pull Request: https://github.com/drewbrokke/liferay-portal/pull/1236

When dealing with roles assigned to millions of users, the Users_Roles table lookup mya perform poorly.  Similar to the solution from LPS-95581, we should use a search index instead, to return the same results faster.

Steps to reproduce:

1. Create millions of users and assign each of them a few new regular roles.
2. Navigate to the roles page in the control panel.

Expected results: The page loads and displays how many users belong to each role

Actual results: The page takes a very long time to load, or the proxy server times out first.

Reproduced in master: 211d833d9105045a1d9ed96bee2ce1b4cde6a41a

Although this issue is difficult to reproduce, we have confirmation from a customer the solution works.  It's also been demonstrated to work in LPS-95581, since the solutions are almost identical (only different tables being used).