title: MySQL performance is very bad
body: <p>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?</p>
  
  <pre><code>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)&gt;=2015
      --  or year( FROM_UNIXTIME(sprintdest.END_DATE/1000) /1000)&gt;=2015
      -- where
      -- j.pkey='CLQ-41441'
     group by
     j.id,
     c.id,il.id,sprint.id
  </code></pre>
  
  <p>Execution Plan</p>
  
  <pre><code>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
  </code></pre>