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, 
    51182, 
    51180, 
    51179, 
    51178, 
    51177, 
    93, 
    51183, 
    20, 
    51188, 
    51187, 
    51186, 
    51185, 
    51184, 
    10, 
    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, 
    51289, 
    51288, 
    51287, 
    51286, 
    51285, 
    51284, 
    51283, 
    8, 
    51294, 
    51293, 
    51291, 
    51290, 
    94, 
    142, 
    62, 
    141, 
    23, 
    16, 
    54, 
    51313, 
    51312, 
    51314, 
    51311, 
    51310, 
    86, 
    51344, 
    120, 
    51345, 
    119, 
    114, 
    108, 
    4, 
    107, 
    51348, 
    121, 
    89, 
    51390, 
    51391, 
    51392, 
    51393, 
    51394
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00403

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "197.14"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "59.01"
      },
      "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": 59,
            "filtered": "21.40",
            "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,51182,51180,51179,51178,51177,93,51183,20,51188,51187,51186,51185,51184,10,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,51289,51288,51287,51286,51285,51284,51283,8,51294,51293,51291,51290,94,142,62,141,23,16,54,51313,51312,51314,51311,51310,86,51344,120,51345,119,114,108,4,107,51348,121,89,51390,51391,51392,51393,51394))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "5.90",
              "prefix_cost": "138.14",
              "data_read_per_join": "944"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
4 6,40M,42
7 7,40M,42
8 7,40M,42,390
10 6,40M,42
12 7,40M,42,390
16 6,40M,44
20 9,40M,43
23 6,40M,42
32 7,40M,42
33 7,40M,42
34 7,40M,42
35 7,40M,44
36 7,40M,43
52 9,40M,42
54 24,40M,44
62 6,40M,42
86 29,40M,43,394
89 25,40M,42
93 9,40M,42
94 7,40M,43
107 33,40M,42
108 33,40M,42
110 7,40M,42
114 6,40M,42
119 6,40M,42
120 6,40M,42
121 6,40M,42
128 10,40M,43
130 10,40M,42
141 6,40M,42
142 6,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
51177 9,40M,42
51178 9,40M,42
51179 9,40M,42
51180 9,40M,42
51182 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
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
51310 24,40M,44
51311 24,40M,44
51312 24,40M,44
51313 24,40M,44
51314 24,40M,44
51344 6,40M,42
51345 6,40M,42
51348 6,40M,42
51390 25,40M,42
51391 25,40M,42
51392 25,40M,42
51393 25,40M,42
51394 25,40M,42