How to find the issues with most issue links or comments in the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
It's very common to look for ticket outliers that can impact system performance such as issues with thousands of links or comments that might've been added by faulty automation or simply kept growing overtime. It is not unusual for issues with lots of associated links or comments cause threads to hang or connection to timeout before all the data is fetched.
Solution
We recommend testing these queries in your staging environment. They were created on PostgreSQL and may need tuning depending on your database. These will help you to identify issues that can be expensive when fetching their information from the database, so you can review them and potentially take actions to remediate the problem (e.g. deleting or archiving those issues, deleting excessive comments, links or attachments).
Issues with most comments
Query tested on PostgreSQL, MySQL, and Oracle.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
count(*),
ji.id,
p.pkey || '-' || ji.issuenum AS issuekey
FROM
jiraaction ja
JOIN jiraissue ji ON ja.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
ji.id,
ji.issuenum,
p.pkey || '-' || ji.issuenum,
p.pname
ORDER BY
count(*)
DESC
LIMIT 50;Query tested on Microsoft SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
jiraaction ja
JOIN jiraissue ji ON ja.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
ji.id,
ji.issuenum,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(*)
DESC;Issues with most attachments
Query tested on PostgreSQL, MySQL, and Oracle.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
issueid,
count(*),
p.pkey || '-' || ji.issuenum AS issuekey
FROM
fileattachment fa
JOIN jiraissue ji ON fa.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
issueid,
ji.issuenum,
p.pkey || '-' || ji.issuenum,
p.pname
ORDER BY
count(issueid)
DESC
LIMIT 50;Query tested on Microsoft SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT TOP 50
issueid,
count(*),
CONCAT(p.pkey,'-',ji.issuenum)
FROM
fileattachment
JOIN jiraissue ji ON fa.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
issueid,
ji.issuenum,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(issueid)
DESC;Issues with most INWARD links
Query tested on PostgreSQL.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
count(*),
ji.id,
p.pkey||'-'||ji.issuenum as issuekey
FROM
issuelink il
join jiraissue ji on il.destination=ji.id
join project p on ji.project=p.id
GROUP BY
il.destination,
ji.id,p.pkey||'-'||ji.issuenum,p.pname
ORDER BY
count(*)
DESC
LIMIT 50;Query tested on Microsoft SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
issuelink il
join jiraissue ji on il.destination=ji.id
join project p on ji.project=p.id
GROUP BY
il.destination,
ji.id,CONCAT(p.pkey,'-',ji.issuenum),p.pname
ORDER BY
count(*)
DESC;Issues with most OUTWARD links
Query tested on PostgreSQL.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
count(*),
ji.id,
p.pkey||'-'||ji.issuenum as issuekey
FROM
issuelink il
join jiraissue ji on il.source=ji.id
join project p on ji.project=p.id
GROUP BY
il.source,
ji.id,
p.pkey||'-'||ji.issuenum,
p.pname
ORDER BY
count(*)
DESC
LIMIT 50;Query tested on Microsoft SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
issuelink il
join jiraissue ji on il.source=ji.id
join project p on ji.project=p.id
GROUP BY
il.source,
ji.id,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(*)
DESC;Was this helpful?