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 (
    130, 51106, 51107, 51112, 51111, 51110, 
    51109, 51108, 128, 51118, 51117, 51116, 
    51115, 51114, 51113, 51165, 51164, 
    51163, 51162, 51161, 52, 51183, 20, 
    51188, 51187, 51186, 51185, 51184, 
    51232, 12, 51238, 51237, 51236, 51235, 
    51234, 51233, 51243, 51242, 51241, 
    51240, 51239, 36, 51246, 51245, 51244, 
    35, 51248, 51247, 51253, 51252, 51251, 
    51250, 51249, 34, 51256, 51255, 51254, 
    33, 32, 51265, 51264, 51263, 51262, 
    51261, 51260, 51259, 110, 51273, 147, 
    51277, 51276, 51275, 51274, 51281, 
    51280, 51279, 51278, 51282, 7, 16, 114, 
    89, 51390, 51391, 51392, 51393, 51394
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00591

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "178.02"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "39.88"
      },
      "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": 39,
            "filtered": "14.46",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (130,51106,51107,51112,51111,51110,51109,51108,128,51118,51117,51116,51115,51114,51113,51165,51164,51163,51162,51161,52,51183,20,51188,51187,51186,51185,51184,51232,12,51238,51237,51236,51235,51234,51233,51243,51242,51241,51240,51239,36,51246,51245,51244,35,51248,51247,51253,51252,51251,51250,51249,34,51256,51255,51254,33,32,51265,51264,51263,51262,51261,51260,51259,110,51273,147,51277,51276,51275,51274,51281,51280,51279,51278,51282,7,16,114,89,51390,51391,51392,51393,51394))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "3.99",
              "prefix_cost": "138.14",
              "data_read_per_join": "638"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
7 7,40M,42
12 7,40M,42,390
16 6,40M,44
20 9,40M,43
32 7,40M,42
33 7,40M,42
34 7,40M,42
35 7,40M,44
36 7,40M,43
52 9,40M,42
89 25,40M,42
110 7,40M,42
114 6,40M,42
128 10,40M,43
130 10,40M,42
147 7,40M,42
51106 10,40M,42
51107 10,40M,42
51108 10,40M,42
51109 10,40M,42
51110 10,40M,42
51111 10,40M,42
51112 10,40M,42
51113 10,40M,43
51114 10,40M,43
51115 10,40M,43
51116 10,40M,43
51117 10,40M,43
51118 10,40M,43
51161 9,40M,42
51162 9,40M,42
51163 9,40M,42
51164 9,40M,42
51165 9,40M,42
51183 9,40M,43
51184 9,40M,43
51185 9,40M,43
51186 9,40M,43
51187 9,40M,43
51188 9,40M,43
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
51239 7,40M,43
51240 7,40M,43
51241 7,40M,43
51242 7,40M,43
51243 7,40M,43
51244 7,40M,44
51245 7,40M,44
51246 7,40M,44
51247 7,40M,44
51248 7,40M,44
51249 7,40M,42
51250 7,40M,42
51251 7,40M,42
51252 7,40M,42
51253 7,40M,42
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
51273 7,40M,42
51274 7,40M,42
51275 7,40M,42
51276 7,40M,42
51277 7,40M,42
51278 7,40M,42
51279 7,40M,42
51280 7,40M,42
51281 7,40M,42
51282 7,40M,42
51390 25,40M,42
51391 25,40M,42
51392 25,40M,42
51393 25,40M,42
51394 25,40M,42