How to find users and filters that have specific fields visible on Search Results through the database on Jira
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
As a consequence of the problems described on article JQL Issue searches are very slow only for a specific user (Server and Data Center), an admin may need to review which users or filters have a specific field being displayed on their searches. This can be relevant since some fields can affect performance when displayed for a large result set - commonly scripted fields or third-party fields.
This article refers to the columns visible on the Issue Navigator as in the screenshot below:
Environment
Jira 8.20.20, 9.4.14, 9.12.2
Solution
These queries where written and tested for PostgreSQL but they may require minor tweak depending on your database product and your configuration.
For reference:
- columnlayoutitem table contains a record for each column layout ID and column they have on their Issue Search. The customfield is stored is the "customfield_xxxx" format under the fieldidentifier column.
- columnlayout links a user or a filter with the the records from the table above.
Find users with the field on their Issue Navigator configuration
- Replace xxxxx with the ID of the customfield.
select cl.username,au.lower_user_name, cli.fieldidentifier from columnlayout cl
join columnlayoutitem cli on cl.id=cli.columnlayout
join app_user au on cl.username=au.user_key
where cli.fieldidentifier in ('customfield_xxxxx');Find filters with the field on their Issue Navigator column configuration
- This does not affect the filter results nor does is affect performance when the filter is used as a board or plan source. It's only used when the user has the Filter option selected:
- Replace xxxxx with the ID of the customfield.
select sr.filtername,sr.id,sr.authorname, au.lower_user_name, cli.fieldidentifier from columnlayout cl
join columnlayoutitem cli on cl.id=cli.columnlayout
join searchrequest sr on cl.searchrequest=sr.id
join app_user au on sr.authorname=au.user_key
where cli.fieldidentifier='customfield_xxxxx';