Welcome fellow stop players!
Here's the db schema (it comes down to the appropriate fields) to help illustrate my riddle:
- The web store has
items , and items have item_options . - The same
item may appear in several categories . categories , items and item_options can be active or inactive (BOOL).
Categories look like this (pay attention to the parent nest where the fruit seeds are inside the 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
What I want is a quick list of all active categories (id, parent_id and name) and the number of active elements in each category containing the active item_options elements.
The query result will look like this:
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
This request works, but takes ~ 430 ms :
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;
This following request only takes ~ 55 ms, but does not include top-level categories (where 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`;
Does anyone see how to speed up the first request or fix the second?
source share