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 (
    4304, 4305, 4316, 4317, 4369, 4363, 4370, 
    4377, 4373, 4400, 4399, 4398, 4396, 
    4395, 4405, 4404, 4403, 4392, 4384, 
    4420, 4423, 4426, 4416, 4432, 4438, 
    4440, 4441, 4442, 4443, 4444, 4448, 
    4445, 4446, 4447
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00169

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "78.11"
    },
    "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": 87,
            "rows_produced_per_join": 87,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (4304,4305,4316,4317,4369,4363,4370,4377,4373,4400,4399,4398,4396,4395,4405,4404,4403,4392,4384,4420,4423,4426,4416,4432,4438,4440,4441,4442,4443,4444,4448,4445,4446,4447))",
            "cost_info": {
              "read_cost": "38.96",
              "eval_cost": "8.70",
              "prefix_cost": "47.66",
              "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": "21.75",
              "eval_cost": "0.44",
              "prefix_cost": "78.11",
              "data_read_per_join": "13K"
            },
            "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
4304 320,166,321M
4305 320,166,321M
4316 320,166,321M
4317 166,320,321M
4363 320,166,321M
4369 320,166,321M
4370 320,166,321M
4373 320,166,321M
4377 320,166,321M
4384 320,166,321M
4392 166,320,321M
4395 166,320,321M
4396 166,320,321M
4398 166,320,321M
4399 320,166,321M
4400 166,320,321M
4403 166,320,321M
4404 166,320,321M
4405 320,166,321M
4416 320,166,321M
4420 320,166,321M
4423 320,166,321M
4426 320,166,321M
4432 320,166,321M
4438 320,166,321M
4440 166,320,321M
4441 320,166,321M
4442 320,166,321M
4443 166,320,321M
4444 320,166,321M
4445 320,166,321M
4446 166,320,321M
4447 320,166,321M
4448 320,166,321M