SELECT 
  cscart_products_categories.product_id, 
  GROUP_CONCAT(
    IF(
      cscart_products_categories.link_type = "M", 
      CONCAT(
        cscart_products_categories.category_id, 
        "M"
      ), 
      cscart_products_categories.category_id
    )
  ) AS category_ids 
FROM 
  cscart_products_categories 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
WHERE 
  cscart_products_categories.product_id IN (
    6447, 6373, 6440, 8714, 8713, 8720, 8718, 
    8719, 8715, 6379, 6334, 6347, 8722, 
    6336, 8506, 6444, 6382, 8721, 6384, 
    6345, 6378, 6335, 6333, 6405
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00173

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "81.21"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 94,
            "rows_produced_per_join": 94,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (6447,6373,6440,8714,8713,8720,8718,8719,8715,6379,6334,6347,8722,6336,8506,6444,6382,8721,6384,6345,6378,6335,6333,6405))",
            "cost_info": {
              "read_cost": "38.91",
              "eval_cost": "9.40",
              "prefix_cost": "48.31",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "23.50",
              "eval_cost": "0.47",
              "prefix_cost": "81.21",
              "data_read_per_join": "14K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`storefront_id` in (0,1)) and ((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`))) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
6333 572,372,582M
6334 572,372,582M
6335 372,583M
6336 372,166,174,453,454,190M
6345 572,372,582M
6347 566,372,595M
6373 174,453,372,166,190,454M
6378 166,329,347M
6379 329,453,372,174,166,455,337,527M
6382 372,583M
6384 372,583M
6405 372,583M
6440 174,453,372,166,454,190M
6444 174,453,372,166,454,190M
6447 174,453,372,166,454,190M
8506 454,372,453,174,166,190M
8713 166,453,454,372,174,190M
8714 166,453,454,372,174,190M
8715 166,453,454,372,174,190M
8718 166,453,454,372,174,190M
8719 166,453,454,372,174,190M
8720 166,453,454,372,174,190M
8721 372,166,174,454,453,190M
8722 372,454,453,166,174,190M