MySQL Query Optimization for running total query - How can I reduce query exectuion time?

Question

Problem

I have a query that I pasted below. The problem I face is how can I trim the latency to under the current time of about 10 seconds.

set@ csum = 0;
SELECT Date_format(assigneddate, '%b %d %Y') AS assigneddate, (@csum: = @csum + numactionitems) AS totalactionitems
FROM(
    SELECT assigneddate,
    Sum(numactionitems) AS numactionitems FROM(
        SELECT assigneddate,
        Count( * ) AS numactionitems FROM(
            SELECT *
            FROM(
                SELECT actionitemtitle,
                actionitemstatement,
                altownerid,
                approvalstatement,
                assigneddate,
                assignorid,
                closeddate,
                closurecriteria,
                closurestatement,
                criticality,
                duedate,
                ecd,
                notes,
                ownerid,
                Concat(lastname, ', ', firstname) AS owner,
                cnames2.categoryvalue AS `team`,
                cnames2.categorynameid AS `teamid`,
                cnames3.categoryvalue AS `department`,
                cnames3.categorynameid AS `departmentid`,
                cnames4.categoryvalue AS `source`,
                cnames4.categorynameid AS `sourceid`,
                cnames5.categoryvalue AS `project_phase`,
                cnames5.categorynameid AS `project_phaseid`,
                ac1.actionitemid FROM actionitemcategories AS ac1 INNER JOIN actionitems AS a INNER JOIN users AS u INNER JOIN(
                    SELECT actionitemid AS a2id,
                    categorynameid AS c2 FROM actionitemcategories WHERE categoryid = 195) AS ac2 INNER JOIN categorynames AS cnames2 ON cnames2.categorynameid = ac2.c2 AND ac1.categoryid = 195 AND a.actionitemid = ac2.a2id AND ac1.actionitemid = a.actionitemid AND a.ownerid = u.userid INNER JOIN(
                    SELECT actionitemid AS a3id,
                    categorynameid AS c3 FROM actionitemcategories WHERE categoryid = 200) AS ac3 INNER JOIN categorynames AS cnames3 ON cnames3.categorynameid = ac3.c3 AND ac2.a2id = ac3.a3id INNER JOIN(
                    SELECT actionitemid AS a4id,
                    categorynameid AS c4 FROM actionitemcategories WHERE categoryid = 202) AS ac4 INNER JOIN categorynames AS cnames4 ON cnames4.categorynameid = ac4.c4 AND ac3.a3id = ac4.a4id INNER JOIN(
                    SELECT actionitemid AS a5id,
                    categorynameid AS c5 FROM actionitemcategories WHERE categoryid = 203) AS ac5 INNER JOIN categorynames AS cnames5 ON cnames5.categorynameid = ac5.c5 AND ac4.a4id = ac5.a5id) s WHERE 1 = 1) f GROUP BY assigneddate UNION ALL(
            SELECT a.date AS assigneddate,
            0 AS numactionitems FROM(
                SELECT '2015-03-05' + INTERVAL(a.a + (10 * b.a) + (100 * c.a)) day AS date FROM(
                    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a CROSS JOIN(
                    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b CROSS JOIN(
                    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c) a) ORDER BY assigneddate ASC) t GROUP BY assigneddate LIMIT 282) t
WHERE assigneddate != '0000-00-00'

Purpose of Query

The purpose of this query is to get all records that contain date values and to collect the running count of all records that fall on a certain date. Date values are computed within the sqlfiddle below. It's final purpose is to be displayed in a graph that takes the running total as a line graph. It will be counting upwards so it is a growing graph.

The graph I am displaying it in is called a build-up graph of all my records (action items with date values).

Description of Issue

My problem is that I am getting the results of the query in at least 10 seconds.

Question

How can I accelerate and reduce the latency of the query so that I will not stall the loading of my graph?

Complete Schema and portion of my above query that Runs Successfully

(I am having difficulty getting the main query to run at all on sqlfiddle, though I can run it from my own machine).

http://sqlfiddle.com/#!9/865ee/11

Any help or suggestions would be tremendously appreciated!

EDIT

ADDED Sample Screenshot of my Categories Interface

Category (First Table) has a field called categoryname which assumes one of 4 values can be expanded or deleted which is - Team, Department, Source, Project_Phase.

CategoryName (Second Table) has a field called categoryvalue which is the actual allowed value for each category (First Table)

Example - Team 1, Team 2, Team 3 are categoryvalues within categoryname and corresponding the category of Team.

Category

Answer 1

Start by making that table of dates a permanent table, not a subquery.

This construct performs very poorly, and can usually be turned into JOINs without subqueries:

JOIN ( SELECT ... )
JOIN ( SELECT ... )

This is because there is no index on the subqueries, so full scans are needed.

Provide EXPLAIN for the entire query.

Addenda

A PRIMARY KEY is a key; don't add another key with the same column(s).

EAV schema leads to complexity and sluggishness that you are encountering.

Don't use TINYTEXT; it slows down tmp tables in complex queries; use VARCHAR(255). Don't use VARCHAR(255), use VARCHAR with a realistic limit.

Why do you need both categories and categorynames?