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 (
    10402, 3805, 4011, 4640, 4786, 4903, 
    5298, 5305, 5388, 5420, 8766, 4100, 
    8322, 3524, 3990, 4619, 4710, 4815, 
    5063, 5518, 5862, 10140, 2921, 3095
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00167

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "57.21"
    },
    "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": 64,
            "rows_produced_per_join": 64,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (10402,3805,4011,4640,4786,4903,5298,5305,5388,5420,8766,4100,8322,3524,3990,4619,4710,4815,5063,5518,5862,10140,2921,3095))",
            "cost_info": {
              "read_cost": "28.41",
              "eval_cost": "6.40",
              "prefix_cost": "34.81",
              "data_read_per_join": "1024"
            },
            "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": "16.00",
              "eval_cost": "0.32",
              "prefix_cost": "57.21",
              "data_read_per_join": "10K"
            },
            "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
2921 423,424,450,166M
3095 423,424,451,166M
3524 423,424,450,166M
3805 423,424,450,166M
3990 503,219,493M
4011 167,166,353M
4100 219,507M
4619 167,166,355M
4640 513,166,517M
4710 167,166,532M
4786 245,334,422M
4815 219,497,504M
4903 513,166,516M
5063 513,166,516M
5298 372,552,550M
5305 372,552,550M
5388 372,250,553,554,552,556M
5420 473,577,475M
5518 466,372,573M
5862 423,308,387,250,350,166,383M
8322 631M
8766 658M
10140 234,166,236M
10402 384M