Is this query sufficient to determine that a custom field is not currently used in any JIRA issues in an instance?
select * from customfield cf where not exists (select * from customfieldvalue cfv where cfv.CUSTOMFIELD = cf.ID)
And is this sufficient to see that it was never used historically?
select * from customfield cf where not exists (select * from customfieldvalue cfv where cfv.CUSTOMFIELD = cf.ID) and not exists (select * from changeitem ci where ci.FIELDTYPE = 'custom' and ci.FIELD = cf.cfname)
No. Because you're not looking at the history. That query will show you if a field currently has data, but not if it had data and someone removed it later (that empties out the customfieldvalue line). You can read changeitem for the name of the field to see where it was used.
Given my findings in https://answers.atlassian.com/questions/43719532 it would seem the above query is necessary but not sufficient to define 'is not used'. I found that some custom fields satisfy the above but also impact performance upon their removal. It would seem that they must be somehow used.
Rank fields are different - once you've installed software, you'll find they have always been used.
For other custom fields though, it's not as simple as "data = load" as you seem to be implying. A field has overhead, even if there has never been any data in it.
The earlier answers here are correct and valid for the question. But performance of creation is a different matter to whether a field is filled or not.
Since there are so many avenues where a custom field can be referenced, we came up with a type of workaround to help identify used fields. First we use the initial methods used here to identify "possible" non-used fields. The "workaround" that we use is to simply rename the field in question. Typically we just add a prefix to the original Field Name. Usually that allows us to find out if it breaks any filters or projects without the problems that come from deleting the field and finding out afterwords. This can also allow the field to be reused rather than deleted as well.
Badges are a great way to show off community activity, whether you’re a newbie or a Champion.Learn more
@Rachel Wright (Jira Genie), @Billy Poggi (AUG NOVA, DC), and @Dana Jansen (Confluence Queen) are just some of the folks that lead one of the world's most active Atlassian User Group (AUG)....
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs