EXPLAIN SELECT `t`.*, COUNT(DISTINCT ti0.cat_id) AS tag_count
FROM (`catalogue_tags` AS t)
LEFT JOIN `catalogue_tags_items` AS ti0 ON `t`.`id` = `ti0`.`tag_id`
LEFT JOIN `catalogue` AS c ON `c`.`cat_id` = `ti0`.`cat_id`
WHERE `t`.`id` NOT IN (100) 
AND `t`.`parent_id` NOT IN (100) 
GROUP BY `t`.`id`
ORDER BY `t`.`parent_id`, COUNT(DISTINCT ti0.cat_id) DESC, `t`.`title` 

EXPLAIN SELECT c.*, cs2.id AS csid, cs2.parent, cs2.shop, cs2.sort, COUNT(DISTINCT ci.item_id) AS count_items, GROUP_CONCAT(DISTINCT cs.shop) AS shops
FROM (`catalogue_categories` AS c)
LEFT JOIN `catalogue_categories_join_shop` AS cs ON `cs`.`category` = `c`.`id`
RIGHT JOIN `catalogue_categories_join_shop` AS cs2 ON `cs2`.`category` = `c`.`id` AND cs2.shop = 1
LEFT JOIN `catalogue_categories_items` AS ci ON `ci`.`cat_id` = `c`.`id`
WHERE `c`.`status` IN (1) 
GROUP BY `cs`.`category`, `cs2`.`category`, `ci`.`cat_id`
ORDER BY `cs2`.`shop`, `cs2`.`parent`, `cs2`.`sort` ASC

SELECT c.*, cs2.id AS csid, cs2.parent, cs2.shop, cs2.sort, COUNT(DISTINCT ci.item_id) AS count_items, GROUP_CONCAT(DISTINCT cs.shop) AS shops
FROM (`catalogue_categories` AS c)
LEFT JOIN `catalogue_categories_join_shop` AS cs ON `cs`.`category` = `c`.`id`
RIGHT JOIN `catalogue_categories_join_shop` AS cs2 ON `cs2`.`category` = `c`.`id` AND cs2.shop = 1
LEFT JOIN `catalogue_categories_items` AS ci ON `ci`.`cat_id` = `c`.`id`
WHERE `c`.`status` IN (1) 
GROUP BY `cs`.`category`, `cs2`.`category`, `ci`.`cat_id`
ORDER BY `cs2`.`shop`, `cs2`.`parent`, `cs2`.`sort` ASC

SELECT c.*, cs.id AS csid, max(if(cs.shop=1, cs.parent,null)) AS parent, cs.shop, cs.sort, COUNT(DISTINCT ci.item_id) AS count_items, GROUP_CONCAT(DISTINCT cs.shop) AS shops
FROM (`catalogue_categories` AS c)
LEFT JOIN `catalogue_categories_join_shop` AS cs ON `cs`.`category` = `c`.`id` AND cs.shop = 1
LEFT JOIN `catalogue_categories_items` AS ci ON `ci`.`cat_id` = `c`.`id`
WHERE `c`.`status` IN (1) AND cs.parent IS NOT NULL
GROUP BY `cs`.`category`, `cs`.`category`, `ci`.`cat_id`
ORDER BY parent, `cs`.`sort` ASC