Problem Definition

When there are issues with thousands of comments, a user can expect bad performance from that issue. Looking into the queries, the following query is run everytime there is a new comment on the issue :

SELECT ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum FROM jiraaction WHERE issueid=216730 AND actiontype='comment' ORDER BY CREATED ASC LIMIT 1 ;

Looks like the query is being indexed by 'CREATED' however this column does not have an index on it.

Suggested Solution

If the query is changed to order by 'ID' then we get much better query performance :

EXPLAIN SELECT ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum FROM jiraaction WHERE issueid=216730 AND actiontype='comment' ORDER BY ID DESC LIMIT 1 ;

This change speeds the query up by a factor of x20.