MySQL performance is very bad

Question

PFB a sql query I am trying to run. The final output returns around 60k rows, but it takes close to 5 hours to run. There is no problem with the connections and stuff like that and I feel that my query needs to be optimized heavily. Can somebody please point me in the right direction?

SELECT
    rapidview.name AS RapidView,
    CASE
        WHEN linktype.LINKNAME ="jira_subtask_link"
        THEN sprintdest.name
        ELSE sprint.name
    END    AS Sprint,
    j.pkey AS CaseKey,
    -- Sub task arent assigned sprint details, they are directly pulled from parent task, so that
    -- logic is implemented here for pulling all sprint related info
    CASE
        WHEN linktype.LINKNAME ="jira_subtask_link"
        THEN FROM_UNIXTIME(sprintdest.start_date/1000)
        ELSE FROM_UNIXTIME(sprint.start_date/1000)
    END AS SprintStartDate,
    CASE
        WHEN linktype.LINKNAME ="jira_subtask_link"
        THEN FROM_UNIXTIME(sprintdest.END_DATE/1000)
        ELSE FROM_UNIXTIME(sprint.END_DATE/1000)
    END                     AS SprintEndDate,
    StoryPoints.numbervalue AS StoryPoint,
    c.cname                 AS Component,
    it.pname                AS Type,
    p.pname                 AS Project,
    iss.pname               AS Status,
    dest.pkey               AS linkedissue,
    dest.id                 AS destid,
    dest.created            AS linkedissuecreated,
    (cglinkedissue.created) AS LinkedIssueClosedDate,
    linktype.LINKNAME       AS LinkType,
    cfoowner.customvalue    AS Owner,
    j.created               AS Created,
    cg.created              AS ClosedDate,
    CASE
        WHEN linktype.LINKNAME ="jira_subtask_link"
        THEN (
                CASE
                    WHEN sprintdest.started=true
                    AND sprintdest.closed=false
                    THEN "Current Sprint"
                    WHEN sprintdest.started=true
                    AND sprintdest.closed=true
                    THEN "Completed Sprint"
                    WHEN sprintdest.started=false
                    AND sprintdest.closed=false
                    THEN "Future Sprint"
                END)
        ELSE (
                CASE
                    WHEN sprint.started=true
                    AND sprint.closed=false
                    THEN "Current Sprint"
                    WHEN sprint.started=true
                    AND sprint.closed=true
                    THEN "Completed Sprint"
                    WHEN sprint.started=false
                    AND sprint.closed=false
                    THEN "Future Sprint"
                END)
    END                         AS SprintStatus,
    j.TIMEORIGINALESTIMATE/3600 AS EstimatedTime,
    j.TIMEESTIMATE/3600         AS RemainingTime,
    j.TIMESPENT/3600            AS LoggedHours ,
    cg.id                       AS CGID,
    ci.groupid                  AS cigroupid,
    ci.field                    AS CIFIELD,
    ci.newstring                AS NEWSTRING
    -- DevLead.stringvalue as DevLead,
    -- PMLead.stringvalue as PMLead,
    -- QaLead.stringvalue as QALead,
    -- DevLeadName.display_name as DevleadDisplayName,
    -- PMLeadName.display_name as PMLeadDisplayName,
    -- QALeadName.display_name as QALeadDisplayName
FROM
    jiraissue j
LEFT JOIN
    customfieldvalue cfv
ON
    cfv.issue=j.id
AND cfv.customfield=11002
LEFT JOIN
    AO_60DB71_SPRINT sprint
ON
    sprint.id=cfv.stringvalue
LEFT JOIN
    AO_60DB71_RAPIDVIEW rapidview
ON
    sprint.RAPID_VIEW_ID=rapidview.id
LEFT JOIN
    nodeassociation na
ON
    j.id=na.source_node_id
AND na.association_type = ('IssueComponent')
LEFT JOIN
    component c
ON
    na.sink_node_id=c.id
LEFT JOIN
    customfieldvalue StoryPoints
ON
    j.id=StoryPoints.issue
AND StoryPoints.customfield=10572
    /*
    LEFT JOIN
    customfieldvalue PMLead
    ON
    j.id=PMLead.issue
    AND PMLead.customfield=10382
    LEFT JOIN
    customfieldvalue DevLead
    ON
    j.id=DevLead.issue
    AND StoryPoints.customfield=10380
    LEFT JOIN
    customfieldvalue QaLead
    ON
    j.id=QaLead.issue
    AND QaLead.customfield=10381
    left join cwd_user DevLeadName
    on DevLead.stringvalue=DevLeadName.user_name
    left join cwd_user PMLeadName
    on PMLead.stringvalue=PMLeadName.user_name
    left join cwd_user QALeadName
    on QaLead.stringvalue=QALeadName.user_name
    */
LEFT JOIN
    issuetype it -- To pull in issuetype
ON
    j.issuetype=it.id
LEFT JOIN
    project p -- To pull in project
ON
    j.project=p.id
LEFT JOIN
    issuestatus iss -- To pull in Case Status
ON
    j.issuestatus=iss.id
LEFT JOIN
    issuelink il -- To identify linked cases
ON
    j.id=il.destination
LEFT JOIN
    issuelinktype linktype
ON
    il.linktype=linktype.id
LEFT JOIN
    jiraissue dest -- To idenfity component for the the linked case
ON
    dest.id=il.source
LEFT JOIN
    customfieldvalue owner -- To pull in customfields
ON
    j.id=owner.issue
AND owner.customfield=10310
LEFT JOIN
    customfieldoption cfoowner -- To pull in customfields
ON
    cfoowner.id=owner.stringvalue
LEFT JOIN
    changegroup cg -- To pull in case history to identify status changes
ON
    j.id=cg.issueid
LEFT JOIN
    changeitem ci
ON
    cg.id=ci.groupid
AND ci.field='status'
AND ci.newstring LIKE '%Closed%'
LEFT JOIN
    changegroup cglinkedissue -- To pull in case history to identify status changes
ON
    dest.id=cglinkedissue.issueid
LEFT JOIN
    changeitem cilinkedissue
ON
    cilinkedissue.groupid=cglinkedissue.id
AND cilinkedissue.field='status'
AND cilinkedissue.newstring LIKE '%Closed%'
LEFT JOIN
    customfieldvalue cfvdest
ON
    cfvdest.issue=dest.id
AND cfvdest.customfield=11002
LEFT JOIN
    AO_60DB71_SPRINT sprintdest
ON
    sprintdest.id=cfvdest.stringvalue
    --  year( FROM_UNIXTIME(sprint.END_DATE/1000) /1000)>=2015
    --  or year( FROM_UNIXTIME(sprintdest.END_DATE/1000) /1000)>=2015
    -- where
    -- j.pkey='CLQ-41441'
   group by
   j.id,
   c.id,il.id,sprint.id

Execution Plan

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  j   ALL (null)  (null)  (null)  (null)  891945  (null)
1   SIMPLE  cfv ref cfvalue_issue   cfvalue_issue   18  jira_rnd_p.j.ID,const   1   (null)
1   SIMPLE  sprint  eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.cfv.STRINGVALUE  1   Using where
1   SIMPLE  rapidview   eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.sprint.RAPID_VIEW_ID 1   (null)
1   SIMPLE  na  ref PRIMARY,node_source PRIMARY 8   jira_rnd_p.j.ID 1   Using where; Using index
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.na.SINK_NODE_ID  1   (null)
1   SIMPLE  StoryPoints ref cfvalue_issue   cfvalue_issue   18  jira_rnd_p.j.ID,const   1   (null)
1   SIMPLE  it  eq_ref  PRIMARY PRIMARY 182 jira_rnd_p.j.issuetype  1   Using where
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.j.PROJECT    1   (null)
1   SIMPLE  iss eq_ref  PRIMARY PRIMARY 182 jira_rnd_p.j.issuestatus    1   Using where
1   SIMPLE  il  ref issuelink_dest  issuelink_dest  9   jira_rnd_p.j.ID 1   (null)
1   SIMPLE  linktype    eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.il.LINKTYPE  1   (null)
1   SIMPLE  dest    eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.il.SOURCE    1   (null)
1   SIMPLE  owner   ref cfvalue_issue   cfvalue_issue   18  jira_rnd_p.j.ID,const   1   (null)
1   SIMPLE  cfoowner    eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.owner.STRINGVALUE    1   Using where
1   SIMPLE  cg  ref chggroup_issue  chggroup_issue  9   jira_rnd_p.j.ID 4   (null)
1   SIMPLE  ci  ref chgitem_chggrp,chgitem_field    chgitem_chggrp  9   jira_rnd_p.cg.ID    1   Using where
1   SIMPLE  cglinkedissue   ref chggroup_issue  chggroup_issue  9   jira_rnd_p.dest.ID  4   (null)
1   SIMPLE  cilinkedissue   ref chgitem_chggrp,chgitem_field    chgitem_chggrp  9   jira_rnd_p.cglinkedissue.ID 1   Using where
1   SIMPLE  cfvdest ref cfvalue_issue   cfvalue_issue   18  jira_rnd_p.dest.ID,const    1   (null)
1   SIMPLE  sprintdest  eq_ref  PRIMARY PRIMARY 8   jira_rnd_p.cfvdest.STRINGVALUE  1   Using where

Well, that's a lot of joins. Look at execution plan, see where an index might help. — Thilo, Oct 30 '15 at 02:51
Pardon me for formatting, because I am new to stackoverflow for posting questions ! — Adithya Kumar, Oct 30 '15 at 02:52
Hey Thilo, Have pasted the execution plan. Can you help as I am new to SQL tuning — Adithya Kumar, Oct 30 '15 at 03:09

score 0 · Answer 1 · answered Oct 30 '15 at 02:53

0

Try EXPLAIN.

Pay attention to possible_keys, key, rows.

Maybe you can post the EXPLAIN result, and we can see what to do.

answered Oct 30 '15 at 02:53

Billy Ren

48
6

Thanks Billy, I have added the explain result. – Adithya Kumar Oct 30 '15 at 03:00

score 0 · Answer 2 · answered Jan 25 '16 at 21:25

Do you need LEFT? That is, are all those other tables optional?

If you can get rid of LEFT in certain cases, you can avoid scanning all 891K rows of j.

Are you only interested in "closed" items? If so, the query does not limit itself to them, again because of LEFT.

I would start by removing LEFT wherever practical. Then move the AND clauses that are not really part of the JOIN to a WHERE on the end. This might allow the query to filter stuff sooner, rather than lugging 891K (or more) rows (including lots of NULLs) around before getting to the GROUP BY.

MySQL performance is very bad

2 Answers2