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, 
    111, 
    51212, 
    144, 
    51215, 
    51214, 
    51213, 
    51444, 
    51445, 
    51446, 
    51447, 
    51220, 
    51219, 
    51218, 
    51217, 
    51216, 
    146, 
    51448, 
    51449, 
    51221, 
    137, 
    51224, 
    51223, 
    51231, 
    51230, 
    51229, 
    51228, 
    51227, 
    51226, 
    51225, 
    109, 
    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, 
    51272, 
    51271, 
    51270, 
    51269, 
    51268, 
    51267, 
    51266, 
    145, 
    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
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00397

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "205.30"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "67.16"
      },
      "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": 67,
            "filtered": "24.35",
            "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,111,51212,144,51215,51214,51213,51444,51445,51446,51447,51220,51219,51218,51217,51216,146,51448,51449,51221,137,51224,51223,51231,51230,51229,51228,51227,51226,51225,109,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,51272,51271,51270,51269,51268,51267,51266,145,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))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "6.72",
              "prefix_cost": "138.14",
              "data_read_per_join": "1K"
            },
            "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
34 7,40M,42
35 7,40M,44
36 7,40M,43
94 7,40M,43
109 7,40M,42
110 7,40M,42
111 7,40M,42
124 7,40M,42
125 7,40M,42
137 7,40M,42
144 7,40M,42,390
145 7,40M,42,390
146 7,40M,42,390
147 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
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
51221 7,40M,42
51223 7,40M,42
51224 7,40M,42
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
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
51266 7,40M,42,390
51267 7,40M,42,390
51268 7,40M,42,390
51269 7,40M,42,390
51270 7,40M,42,390
51271 7,40M,42,390
51272 7,40M,42,390
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
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