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 (
    128, 51118, 51117, 51116, 51115, 51114, 
    51113, 51165, 51164, 51163, 51162, 
    51161, 52, 51183, 20, 51188, 51187, 
    51186, 51185, 51184, 51194, 51193, 
    51192, 51191, 51190, 51189, 19, 51201, 
    51200, 51199, 51198, 51197, 51196, 
    51195, 131, 111, 51212, 144, 51215, 
    51214, 51213, 51444, 51445, 51446, 
    51447, 51220, 51219, 51218, 51217, 
    51216, 146, 51448, 51449, 51231, 51230, 
    51229, 51228, 51227, 51226, 51225, 
    109, 51243, 51242, 51241, 51240, 51239, 
    36, 51253, 51252, 51251, 51250, 51249, 
    34, 51265, 51264, 51263, 51262, 51261, 
    51260, 51259, 110, 51273, 147, 51277, 
    51276, 51275, 51274, 9, 59, 60, 13, 51346, 
    118, 114, 84, 51347, 85, 61, 51350, 123, 
    15, 95, 51352, 51351, 96
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00395

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "187.51"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "49.37"
      },
      "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": 49,
            "filtered": "17.90",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (128,51118,51117,51116,51115,51114,51113,51165,51164,51163,51162,51161,52,51183,20,51188,51187,51186,51185,51184,51194,51193,51192,51191,51190,51189,19,51201,51200,51199,51198,51197,51196,51195,131,111,51212,144,51215,51214,51213,51444,51445,51446,51447,51220,51219,51218,51217,51216,146,51448,51449,51231,51230,51229,51228,51227,51226,51225,109,51243,51242,51241,51240,51239,36,51253,51252,51251,51250,51249,34,51265,51264,51263,51262,51261,51260,51259,110,51273,147,51277,51276,51275,51274,9,59,60,13,51346,118,114,84,51347,85,61,51350,123,15,95,51352,51351,96))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "4.94",
              "prefix_cost": "138.14",
              "data_read_per_join": "789"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
9 6,40M,42
13 6,40M,42
15 6,40M,43
19 9,40M,44
20 9,40M,43
34 7,40M,42
36 7,40M,43
52 9,40M,42
59 6,40M,42
60 6,40M,42
61 6,40M,42
84 28,40M,42,394
85 28,40M,42,394
95 31,40M,43
96 32,40M,42
109 7,40M,42
110 7,40M,42
111 7,40M,42
114 6,40M,42
118 6,40M,42
123 6,40M,42
128 10,40M,43
131 9,40M,42
144 7,40M,42,390
146 7,40M,42,390
147 7,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
51189 9,40M,44
51190 9,40M,44
51191 9,40M,44
51192 9,40M,44
51193 9,40M,44
51194 9,40M,44
51195 9,40M,42
51196 9,40M,42
51197 9,40M,42
51198 9,40M,42
51199 9,40M,42
51200 9,40M,42
51201 9,40M,42
51212 7,40M,42
51213 7,40M,42,390
51214 7,40M,42,390
51215 7,40M,42,390
51216 7,40M,42,390
51217 7,40M,42,390
51218 7,40M,42,390
51219 7,40M,42,390
51220 7,40M,42,390
51225 7,40M,42
51226 7,40M,42
51227 7,40M,42
51228 7,40M,42
51229 7,40M,42
51230 7,40M,42
51231 7,40M,42
51239 7,40M,43
51240 7,40M,43
51241 7,40M,43
51242 7,40M,43
51243 7,40M,43
51249 7,40M,42
51250 7,40M,42
51251 7,40M,42
51252 7,40M,42
51253 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
51346 6,40M,42
51347 28,40M,42,394
51350 6,40M,42
51351 32,40M,42
51352 32,40M,42
51444 7,40M,42,390
51445 7,40M,42,390
51446 7,40M,42,390
51447 7,40M,42,390
51448 7,40M,42,390
51449 7,40M,42,390