JIRA search slowness when JIRA has a huge number of issue security levels

Summary

When a large number of Issue Security Levels are used, the performance of JIRA degrades.

Steps to Reproduce

This issue was observed with the following:

1. 40,000 Issues
2. ~200+ Groups
3. ~2,000 Users
4. More than 20 Security Levels (with group and project roles).

With this configuration, attempting to open the "Assign To Me" gadget would take more than 10 seconds.

Expected Results

The performance of JIRA should not be adversely impacted by Security levels.

Actual Results

Defining many Security Levels leads to a degradation of performance with JIRA.

Notes

This is a known behaviour and is documented under: Configuring Issue-level Security

Diagnostics

1. Executing the below SQL on the instance will identify the total number of issue security levels:

   select scheme, count(security) from schemeissuesecurities group by scheme;
   

2. If this is 20 or higher it's likely you're encountering this bug.

Workarounds:

1. Reduce the issue security levels.
2. Reduce the number of groups using in each level