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 (
    51088, 51087, 22713, 51086, 51089, 51090, 
    51209, 51208, 51207, 51206, 124, 144, 
    51215, 51214, 51213, 51444, 51445, 
    51446, 51447, 51220, 51219, 51218, 
    51217, 51216, 146, 51448, 51449, 51221, 
    137, 51224, 51223, 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.00437

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "189.43"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "51.30"
      },
      "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": 51,
            "filtered": "18.60",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (51088,51087,22713,51086,51089,51090,51209,51208,51207,51206,124,144,51215,51214,51213,51444,51445,51446,51447,51220,51219,51218,51217,51216,146,51448,51449,51221,137,51224,51223,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": "5.13",
              "prefix_cost": "138.14",
              "data_read_per_join": "820"
            },
            "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
110 7,40M,42
124 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
22713 7M,40,42
51086 7M,40,42
51087 7M,40,42
51088 7M,40,42
51089 7M,40,42
51090 7M,40,42
51206 7,40M,42
51207 7,40M,42
51208 7,40M,42
51209 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
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