用于运行总查询的 MySQL 查询优化 - 如何减少查询执行时间？

Question

问题

我在下面粘贴了一个查询。我面临的问题是如何将延迟时间缩短到当前时间大约 10 秒以下。

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'

查询的目的

此查询的目的是获取包含日期值的所有记录，并收集属于特定日期的所有记录的运行计数。 日期值在下面的 sqlfiddle 中计算。它的最终目的是显示在一个图表中，该图表将运行总计作为折线图。它将向上计数，因此它是一个不断增长的图表。

我在其中显示它的图表称为我所有记录(具有日期值的操作项)的构建图。

问题描述

我的问题是我至少需要 10 秒才能获得查询结果。

问题

我怎样才能加速和减少查询的延迟，这样我就不会拖延我的图形加载？

完整的模式和我上面成功运行的部分查询

(我很难让主查询在 sqlfiddle 上运行，尽管我可以从我自己的机器上运行它)。

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

如有任何帮助或建议，我们将不胜感激!

编辑

添加了我的类别界面的示例截图

Category(第一个表)有一个名为 categoryname 的字段，它假定可以扩展或删除 4 个值之一，即 Team、Department、Source、Project_Phase。

CategoryName(第二表)有一个名为categoryvalue的字段，它是每个类别(第一表)的实际允许值

示例 - Team 1、Team 2、Team 3 是类别名称中的类别值，对应于团队的类别。

类别

Answer 1

首先将该日期表设为永久表，而不是子查询。

这个结构性能很差，通常可以变成没有子查询的JOIN:

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

这是因为子查询上没有索引，所以需要全扫描。

为整个查询提供 EXPLAIN。

附录

PRIMARY KEY 是一个键；不要添加具有相同列的另一个键。

EAV 模式会导致您遇到的复杂性和缓慢性。

不要使用TINYTEXT；它会减慢复杂查询中的 tmp 表；使用 VARCHAR(255)。不要使用 VARCHAR(255)，使用具有现实限制的 VARCHAR。

为什么同时需要类别和类别名称？