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 (
    2587, 2588, 2589, 2590, 2593, 2594, 2595, 
    2603, 2609, 2610, 2611, 2613, 2614, 
    2617, 2618, 2696, 2697, 2695, 2694, 
    2704, 2705, 2706, 2723, 2724, 2725, 
    2726, 2739, 2740, 2741, 2742, 2743, 
    2744, 2745, 2761, 2762, 2763, 2764, 
    2865, 2872, 2873, 2874, 2875, 3423, 
    3422
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00206

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "99.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": 110,
            "rows_produced_per_join": 110,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (2587,2588,2589,2590,2593,2594,2595,2603,2609,2610,2611,2613,2614,2617,2618,2696,2697,2695,2694,2704,2705,2706,2723,2724,2725,2726,2739,2740,2741,2742,2743,2744,2745,2761,2762,2763,2764,2865,2872,2873,2874,2875,3423,3422))",
            "cost_info": {
              "read_cost": "49.51",
              "eval_cost": "11.00",
              "prefix_cost": "60.51",
              "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.50",
              "eval_cost": "0.55",
              "prefix_cost": "99.01",
              "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
2587 329,166,457M
2588 329,166,457M
2589 166,329,457M
2590 166,329,457M
2593 329,166,457M
2594 166,329,457M
2595 166,329,457M
2603 329,166,457M
2609 329,166,457M
2610 329,166,457M
2611 329,166,457M
2613 329,166,457M
2614 329,166,457M
2617 166,329,457M
2618 329,166,457M
2694 329,166,457M
2695 329,166,457M
2696 329,166,457M
2697 329,166,457M
2704 329,166,457M
2705 329,166,457M
2706 329,166,457M
2723 329,166,457M
2724 329,166,457M
2725 329,166,457M
2726 329,166,457M
2739 329,166,457M
2740 329,166,457M
2741 329,166,457M
2742 329,166,457M
2743 329,166,457M
2744 329,166,457M
2745 329,166,457M
2761 329,166,457M
2762 329,166,457M
2763 329,166,457M
2764 329,166,457M
2865 329,166,457M
2872 329,166,457M
2873 329,166,457M
2874 329,166,457M
2875 329,166,457M
3422 329,166,457M
3423 329,166,457M