SELECT 
  COUNT(DISTINCT p.product_id) 
FROM 
  cscart_tag_links AS tl 
  INNER JOIN cscart_products AS p ON p.product_id = tl.object_id 
  AND p.status = 'A' 
  INNER JOIN cscart_products_categories AS pc ON pc.product_id = p.product_id 
  INNER JOIN cscart_categories AS c ON c.category_id = pc.category_id 
  AND c.status = 'A' 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = p.company_id 
WHERE 
  tl.tag_id = 170067 
  AND tl.object_type = 'P' 
  AND companies.status = 'A' 
  AND companies.company_id IN (1, 3, 5, 6, 7, 8, 10, 11, 12, 13, 15) 
  AND c.storefront_id IN (0, 4) 
  AND NOT EXISTS (
    SELECT 
      1 
    FROM 
      cscart_categories AS mb_sitemap_tag_path_categories 
    WHERE 
      FIND_IN_SET(
        mb_sitemap_tag_path_categories.category_id, 
        REPLACE(c.id_path, '/', ',')
      ) 
      AND mb_sitemap_tag_path_categories.storefront_id NOT IN (0, 4)
  )

Query time 0.00275

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "tl",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "tag_id", "ids", "idx_tag_id", "idx_object_id"],
      "key": "ids",
      "key_length": "6",
      "used_key_parts": ["tag_id", "object_type"],
      "ref": ["const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "tl.object_type = 'P'",
      "using_index": true
    },
    "table": {
      "table_name": "p",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "status", "idx_company_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["devmotorsbay.tl.object_id"],
      "rows": 1,
      "filtered": 98.3818512,
      "attached_condition": "p.`status` = 'A'"
    },
    "table": {
      "table_name": "companies",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["company_id"],
      "ref": ["devmotorsbay.p.company_id"],
      "rows": 1,
      "filtered": 100,
      "index_condition": "companies.company_id in (1,3,5,6,7,8,10,11,12,13,15)",
      "attached_condition": "companies.`status` = 'A'"
    },
    "table": {
      "table_name": "pc",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["devmotorsbay.tl.object_id"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "c",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "p_category_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["devmotorsbay.pc.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "c.storefront_id in (0,4) and !<in_optimizer>(1,exists(subquery#2)) and c.`status` = 'A'"
    },
    "subqueries": [
      {
        "expression_cache": {
          "state": "uninitialized",
          "query_block": {
            "select_id": 2,
            "table": {
              "table_name": "mb_sitemap_tag_path_categories",
              "access_type": "ALL",
              "rows": 445,
              "filtered": 100,
              "attached_condition": "find_in_set(mb_sitemap_tag_path_categories.category_id,replace(c.id_path,'/',',')) and mb_sitemap_tag_path_categories.storefront_id not in (0,4)"
            }
          }
        }
      }
    ]
  }
}