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, 51273, 147, 51277, 51276, 51275, 
    51274, 9, 59, 60, 13, 51346, 118, 114, 
    84, 51347, 85, 61, 51350, 123, 15, 95, 
    51352, 51351, 96, 89, 51390, 51391, 
    51392, 51393, 51394
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00355

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "186.62"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "48.48"
      },
      "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": 48,
            "filtered": "17.58",
            "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,51273,147,51277,51276,51275,51274,9,59,60,13,51346,118,114,84,51347,85,61,51350,123,15,95,51352,51351,96,89,51390,51391,51392,51393,51394))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "4.85",
              "prefix_cost": "138.14",
              "data_read_per_join": "775"
            },
            "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
89 25,40M,42
95 31,40M,43
96 32,40M,42
109 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
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
51390 25,40M,42
51391 25,40M,42
51392 25,40M,42
51393 25,40M,42
51394 25,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