mysql - 你如何优化这个半复杂的 mysql 查询？

Question

问候 flowstackers 伙伴们!

这是帮助说明我的难题的数据库模式(简化为相关字段):

• 网上商店有 items，items 有 item_options。
• 相同的项目可以出现在多个类别中。
• categories、items 和 item_options 都可以是事件的或非事件的 (BOOL)。

类别如下所示(注意 parent_id 嵌套，其中 Fruit Seeds 在 Seeds 内部):

id   parent_id   name              active
 1           0   Seeds                  1
 2           1   Vegetable Seeds        1
 3           1   Fruit Seeds            0
 4           0   Plants                 1
 5           4   Vegetable Plants       1
 6           4   Fruit Plants           1

我想要的是所有事件类别(id、parent_id 和名称)的快速列表，以及包含事件 item_options 的每个类别的事件项目的计数。

查询结果如下所示:

id   parent_id   name              item_count
 1           0   Seeds                      0
 2           1   Vegetable Seeds           52
 4           0   Plants                     0
 5           4   Vegetable Plants         103
 6           4   Fruit Plants              79

此查询有效，但需要大约 430 毫秒:

    SELECT c.`id`, c.`parent_id`, c.`name`,
        (SELECT COUNT(*)
            FROM `item_categories` AS ic
            LEFT JOIN `items` AS i
                ON (i.`id` = ic.`item_id`)
            LEFT JOIN `item_options` AS io
                ON (i.`id` = io.`item_id`)
            WHERE c.`id` = ic.`category_id`
                AND i.`active` = 1
                AND io.`active` = 1
            ) AS `item_count`
    FROM `categories` AS c
    WHERE c.`active` = 1;

下一个查询只用了大约 55 毫秒，但未能包含顶级类别(其中 parent_id = 0):

    SELECT c.`id`, c.`parent_id`, c.`name`,
        COUNT(ic.`item_id`) AS `item_count`
    FROM `categories` AS c
    LEFT JOIN `item_categories` AS ic
        ON (c.`id` = ic.`category_id`)
    LEFT JOIN `items` AS i
        ON (i.`id` = ic.`item_id`)
    LEFT JOIN `item_options` AS io
        ON (i.`id` = io.`item_id`)
    WHERE c.`active` = 1
      AND i.`active` = 1
      AND io.`active` = 1
    GROUP BY c.`id`;

有人知道如何加速第一个查询或修复第二个查询吗？

Answer 1

哦!这似乎做到了......

(
    SELECT c.`id`, c.`parent_id`, c.`name`,
        COUNT(ic.`item_id`) AS `item_count`
    FROM `categories` AS c
    LEFT JOIN `item_categories` AS ic
        ON (c.`id` = ic.`category_id`)
    LEFT JOIN `items` AS i
        ON (i.`id` = ic.`item_id`)
    LEFT JOIN `item_options` AS io
        ON (i.`id` = io.`item_id`)
    WHERE c.`active` = 1
      AND i.`active` = 1
      AND io.`active` = 1
    GROUP BY c.`id`
)
UNION
(
    SELECT c.`id`, c.`parent_id`, c.`name`, 0
    FROM `categories` AS c
    WHERE c.`parent_id` = 0
      AND c.`active` = 1
)
ORDER BY `parent_id`, `NAME`;

有人看到更好的方法吗？ :D