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 (
    7531, 7504, 8342, 8341, 8343, 8344, 8339, 
    8340, 7503, 7539, 7500, 7506, 7669, 
    7530, 7494, 7502, 7501, 8649, 7497, 
    8082, 7932, 7933, 8095, 9387, 7934, 
    7935, 8346, 8349, 8352, 8650, 8348, 
    8350, 8353, 8355, 8347, 8648
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00110

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "38.61"
    },
    "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": 37,
            "rows_produced_per_join": 37,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (7531,7504,8342,8341,8343,8344,8339,8340,7503,7539,7500,7506,7669,7530,7494,7502,7501,8649,7497,8082,7932,7933,8095,9387,7934,7935,8346,8349,8352,8650,8348,8350,8353,8355,8347,8648))",
            "cost_info": {
              "read_cost": "21.96",
              "eval_cost": "3.70",
              "prefix_cost": "25.66",
              "data_read_per_join": "592"
            },
            "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": 1,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "9.25",
              "eval_cost": "0.19",
              "prefix_cost": "38.61",
              "data_read_per_join": "5K"
            },
            "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
7494 623M
7497 623M
7500 623M
7501 623M
7502 623M
7503 623,622,625M
7504 623M
7506 623M
7530 636M
7531 636M
7539 638M
7669 633M
7932 630M
7933 630M
7934 630M
7935 630M
8082 630M
8095 630M
8339 637M
8340 637M
8341 637M
8342 637M
8343 637M
8344 637M
8346 637M
8347 637M
8348 637M
8349 637M
8350 637M
8352 637M
8353 637M
8355 637M
8648 635M
8649 618M
8650 635M
9387 643M