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 (
    5032, 5035, 5378, 5028, 5030, 5029, 5040, 
    5036, 5034, 5042, 5383, 5043, 5047, 
    5044, 5050, 5379, 5033, 5049, 5381, 
    5380, 5382, 5054, 5384, 5385
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00179

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "54.01"
    },
    "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": 60,
            "rows_produced_per_join": 60,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (5032,5035,5378,5028,5030,5029,5040,5036,5034,5042,5383,5043,5047,5044,5050,5379,5033,5049,5381,5380,5382,5054,5384,5385))",
            "cost_info": {
              "read_cost": "27.01",
              "eval_cost": "6.00",
              "prefix_cost": "33.01",
              "data_read_per_join": "960"
            },
            "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": 3,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "15.00",
              "eval_cost": "0.30",
              "prefix_cost": "54.01",
              "data_read_per_join": "9K"
            },
            "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
5028 522,521,535M
5029 522,521,535M
5030 522,521,535M
5032 522,521,535M
5033 522,521,535M
5034 522,521,536M
5035 522,521,537M
5036 522,521,535M
5040 522,521,537M
5042 522,521,537M
5043 522,521,536M
5044 522,521,536M
5047 522,521,535M
5049 522,521,536M
5050 522,521,536M
5054 522,521,535M
5378 538,521,539M
5379 538,521,540M
5380 538,521,539M
5381 538,521,540M
5382 538,521,539M
5383 538,521,539M
5384 538,521,539M
5385 538,521,539M