Query takes forever and creates huge temporary table..

[Yrrk Wahlen]

I'm using Tableau to create a visual chart but it takes forever to run and creates a huge temporary table. So I started poking around and i'm wondering if there is something obvious that I can do to improve performance? I'm not the best at interpreting explain output so any guidance is much appreciated.

mysql> explain SELECT ((YEAR(`jiraissue`.`CREATED`) * 100) + MONTH(`jiraissue`.`CREATED`)) AS `my_CREATED_ok`, `issuetype`.`pname` AS `none_issuetype_pname_nk`, COUNT(DISTINCT `jiraissue`.`ID`) AS `ctd_jiraissue_ID_qk` FROM `project` LEFT JOIN `jiraissue` ON (`project`.`ID` = `jiraissue`.`PROJECT`) LEFT JOIN `issuetype` ON (`jiraissue`.`issuetype` = `issuetype`.`ID`) LEFT JOIN `customfieldvalue` ON (`jiraissue`.`ID` = `customfieldvalue`.`ISSUE`) LEFT JOIN `customfieldvalue` `customfieldvalue1` ON (`jiraissue`.`ID` = `customfieldvalue1`.`ISSUE`) LEFT JOIN `customfieldvalue` `customfieldvalue2` ON (`jiraissue`.`ID` = `customfieldvalue2`.`ISSUE`) WHERE (((NOT ((`project`.`pname` = 'Operations') AND (((YEAR(`jiraissue`.`CREATED`) * 100) + MONTH(`jiraissue`.`CREATED`)) IN (200712, 200801, 200803, 200804, 200805, 200806, 200807, 200808, 200809, 200810, 200811, 200812)))) OR ISNULL(((YEAR(`jiraissue`.`CREATED`) * 100) + MONTH(`jiraissue`.`CREATED`)))) AND ((`jiraissue`.`CREATED` >= TIMESTAMP('2011-12-01 04:24:00')) AND (`jiraissue`.`CREATED` <= TIMESTAMP('2012-02-29 06:46:21'))) AND (LEFT(`project`.`pname`, LENGTH('PLE - Operations')) = 'PLE - Operations')) GROUP BY 1, 2;
+----+-------------+-------------------+--------+-------------------+---------------+---------+------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+--------+-------------------+---------------+---------+------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | jiraissue | ALL | issue_proj_status | NULL | NULL | NULL | 60972 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | project | eq_ref | PRIMARY | PRIMARY | 8 | jiradb40.jiraissue.PROJECT | 1 | Using where |
| 1 | SIMPLE | issuetype | eq_ref | PRIMARY | PRIMARY | 182 | jiradb40.jiraissue.issuetype | 1 | |
| 1 | SIMPLE | customfieldvalue | ref | cfvalue_issue | cfvalue_issue | 9 | jiradb40.jiraissue.ID | 63 | Using index |
| 1 | SIMPLE | customfieldvalue1 | ref | cfvalue_issue | cfvalue_issue | 9 | jiradb40.jiraissue.ID | 63 | Using index |
| 1 | SIMPLE | customfieldvalue2 | ref | cfvalue_issue | cfvalue_issue | 9 | jiradb40.jiraissue.ID | 63 | Using index |
+----+-------------+-------------------+--------+-------------------+---------------+---------+------------------------------+-------+----------------------------------------------+
6 rows in set (0.00 sec)

mysql> show index from jiraissue;
+-----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| jiraissue | 0 | PRIMARY | 1 | ID | A | 60972 | NULL | NULL | | BTREE | |
| jiraissue | 1 | issue_key | 1 | pkey | A | 60972 | NULL | NULL | YES | BTREE | |
| jiraissue | 1 | issue_proj_status | 1 | PROJECT | A | 39 | NULL | NULL | YES | BTREE | |
| jiraissue | 1 | issue_proj_status | 2 | issuestatus | A | 155 | NULL | NULL | YES | BTREE | |
| jiraissue | 1 | issue_assignee | 1 | ASSIGNEE | A | 10162 | NULL | NULL | YES | BTREE | |
| jiraissue | 1 | issue_workflow | 1 | WORKFLOW_ID | A | 60972 | NULL | NULL | YES | BTREE | |
+-----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)