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 (
    12714, 12715, 12716, 12717, 12718, 12719, 
    12720, 12721, 12722, 12723, 12724, 
    12725, 12726, 12727, 12728, 12729, 
    12730, 12731, 12732, 12733, 12734, 
    12735, 12736, 12737, 12738, 12739, 
    12740, 12741, 12742, 12743
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00093

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "53.56"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "6.26"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 46,
            "rows_produced_per_join": 1,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "5.15",
              "eval_cost": "0.18",
              "prefix_cost": "5.33",
              "data_read_per_join": "7K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`test2_gaseus`.`cscart_categories`.`storefront_id` in (0,1)) and ((`test2_gaseus`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`test2_gaseus`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`test2_gaseus`.`cscart_categories`.`usergroup_ids`))) and (`test2_gaseus`.`cscart_categories`.`status` in ('A','H')))"
          }
        },
        {
          "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": 34,
            "rows_produced_per_join": 6,
            "filtered": "10.00",
            "index_condition": "(`test2_gaseus`.`cscart_products_categories`.`product_id` in (12714,12715,12716,12717,12718,12719,12720,12721,12722,12723,12724,12725,12726,12727,12728,12729,12730,12731,12732,12733,12734,12735,12736,12737,12738,12739,12740,12741,12742,12743))",
            "using_join_buffer": "hash join",
            "cost_info": {
              "read_cost": "35.71",
              "eval_cost": "0.63",
              "prefix_cost": "47.30",
              "data_read_per_join": "100"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`test2_gaseus`.`cscart_products_categories`.`category_id` = `test2_gaseus`.`cscart_categories`.`category_id`)"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
12714 283M
12715 283M
12716 283M
12717 283M
12718 283M
12719 283M
12720 283M
12721 283M
12722 283M
12723 283M
12724 283M
12725 283M
12726 283M
12727 283M
12728 283M
12729 283M
12730 283M
12731 283M
12732 283M
12733 283M
12734 283M
12735 283M
12736 283M
12737 283M
12738 283M
12739 283M
12740 283M
12741 283M
12742 283M
12743 283M