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 (
    51083, 22715, 51082, 51085, 51081, 51088, 
    51087, 22713, 51086, 51089, 51090, 
    51092, 51093, 22714, 51091, 51095, 
    51096, 51097, 51098, 51100, 51099, 
    22716, 51103, 22712, 51105, 51102, 
    51101, 51205, 51204, 51203, 51202, 
    125, 51209, 51208, 51207, 51206, 124, 
    51221, 137, 51224, 51223, 51232, 12, 
    51238, 51237, 51236, 51235, 51234, 
    51233, 51246, 51245, 51244, 35, 51248, 
    51247, 51256, 51255, 51254, 33, 32, 
    51265, 51264, 51263, 51262, 51261, 
    51260, 51259, 110, 51281, 51280, 51279, 
    51278, 51282, 7, 51289, 51288, 51287, 
    51286, 51285, 51284, 51283, 8, 51294, 
    51293, 51291, 51290, 94
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00330

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "178.91"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "40.77"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 383,
            "rows_produced_per_join": 15,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "40.09",
              "eval_cost": "1.53",
              "prefix_cost": "41.62",
              "data_read_per_join": "81K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`test_uchur_k`.`cscart_categories`.`storefront_id` in (0,1)) and ((`test_uchur_k`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`test_uchur_k`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`test_uchur_k`.`cscart_categories`.`usergroup_ids`))) and (`test_uchur_k`.`cscart_categories`.`status` in ('A','H')))"
          }
        },
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "test_uchur_k.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 18,
            "rows_produced_per_join": 40,
            "filtered": "14.78",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (51083,22715,51082,51085,51081,51088,51087,22713,51086,51089,51090,51092,51093,22714,51091,51095,51096,51097,51098,51100,51099,22716,51103,22712,51105,51102,51101,51205,51204,51203,51202,125,51209,51208,51207,51206,124,51221,137,51224,51223,51232,12,51238,51237,51236,51235,51234,51233,51246,51245,51244,35,51248,51247,51256,51255,51254,33,32,51265,51264,51263,51262,51261,51260,51259,110,51281,51280,51279,51278,51282,7,51289,51288,51287,51286,51285,51284,51283,8,51294,51293,51291,51290,94))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "4.08",
              "prefix_cost": "138.14",
              "data_read_per_join": "652"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
7 7,40M,42
8 7,40M,42,390
12 7,40M,42,390
32 7,40M,42
33 7,40M,42
35 7,40M,44
94 7,40M,43
110 7,40M,42
124 7,40M,42
125 7,40M,42
137 7,40M,42
22712 7M,40,42
22713 7M,40,42
22714 7M,40,42
22715 7M,40,42
22716 7M,40,42
51081 7M,40,42
51082 7M,40,42
51083 7M,40,42
51085 7M,40,42
51086 7M,40,42
51087 7M,40,42
51088 7M,40,42
51089 7M,40,42
51090 7M,40,42
51091 7M,40,42
51092 7M,40,42
51093 7M,40,42
51095 7M,40,42
51096 7M,40,42
51097 7M,40,42
51098 7M,40,42
51099 7M,40,42
51100 7M,40,42
51101 7M,40,42
51102 7M,40,42
51103 7M,40,42
51105 7M,40,42
51202 7,40M,42
51203 7,40M,42
51204 7,40M,42
51205 7,40M,42
51206 7,40M,42
51207 7,40M,42
51208 7,40M,42
51209 7,40M,42
51221 7,40M,42
51223 7,40M,42
51224 7,40M,42
51232 7,40M,42,390
51233 7,40M,42,390
51234 7,40M,42,390
51235 7,40M,42,390
51236 7,40M,42,390
51237 7,40M,42,390
51238 7,40M,42,390
51244 7,40M,44
51245 7,40M,44
51246 7,40M,44
51247 7,40M,44
51248 7,40M,44
51254 7,40M,42
51255 7,40M,42
51256 7,40M,42
51259 7,40M,42
51260 7,40M,42
51261 7,40M,42
51262 7,40M,42
51263 7,40M,42
51264 7,40M,42
51265 7,40M,42
51278 7,40M,42
51279 7,40M,42
51280 7,40M,42
51281 7,40M,42
51282 7,40M,42
51283 7,40M,42,390
51284 7,40M,42,390
51285 7,40M,42,390
51286 7,40M,42,390
51287 7,40M,42,390
51288 7,40M,42,390
51289 7,40M,42,390
51290 7,40M,43
51291 7,40M,43
51293 7,40M,43
51294 7,40M,43