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 (
    2112, 2111, 2110, 2305, 2304, 2373, 2374, 
    2499, 2500, 2581, 2585, 2586, 2623, 
    2624, 2625, 2626, 2685, 2686, 2687, 
    8417, 8418, 8419, 8420, 8398, 8415, 
    8416, 8421, 2432, 8485, 8484, 8483, 
    2455, 8488, 8487, 8486, 8490, 8489, 
    2438
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00231

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "96.71"
    },
    "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": 109,
            "rows_produced_per_join": 109,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (2112,2111,2110,2305,2304,2373,2374,2499,2500,2581,2585,2586,2623,2624,2625,2626,2685,2686,2687,8417,8418,8419,8420,8398,8415,8416,8421,2432,8485,8484,8483,2455,8488,8487,8486,8490,8489,2438))",
            "cost_info": {
              "read_cost": "47.66",
              "eval_cost": "10.90",
              "prefix_cost": "58.56",
              "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": 5,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "27.25",
              "eval_cost": "0.55",
              "prefix_cost": "96.71",
              "data_read_per_join": "17K"
            },
            "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
2110 322,166,340M
2111 322,166,340M
2112 322,166,340M
2304 166,378,443M
2305 378,166,443M
2373 166,329,347M
2374 329,166,347M
2432 372,174,166,453,454,190M
2438 454,453,174,372,166,190M
2455 454,453,372,166,174,190M
2499 166,234,236M
2500 166,234,236M
2581 166,234,236M
2585 166,234,236M
2586 166,234,236M
2623 166,234,236M
2624 166,234,236M
2625 166,234,236M
2626 166,234,236M
2685 234,166,236M
2686 166,234,236M
2687 166,234,236M
8398 236M
8415 236M
8416 236M
8417 236M
8418 236M
8419 236M
8420 236M
8421 236M
8483 454,166,372,453,174,190M
8484 453,174,454,166,372,190M
8485 453,174,454,166,372,190M
8486 174,372,454,166,453,190M
8487 166,453,174,372,454,190M
8488 166,453,174,372,454,190M
8489 372,174,454,166,453,190M
8490 174,453,166,372,454,190M