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, 
    51092, 51093, 22714, 51091, 51095, 
    51103, 22712, 51105, 51102, 51101, 
    51155, 51154, 51153, 51152, 21, 51158, 
    51157, 51156, 53, 51160, 51159, 51297, 
    51296, 139, 51300, 51299, 51298, 51304, 
    51303, 51302, 51301, 138, 51443, 51309, 
    51308, 51307, 51306, 51305, 129, 51318, 
    51317, 51316, 51, 51319, 51323, 51322, 
    51321, 51320, 50, 51324, 51497, 51498, 
    51564, 51568, 51569, 51570, 51571, 
    51572, 51573, 51565, 51574, 51575, 
    51576, 51577, 51566, 51578, 51579, 
    51580, 51581, 51567, 51582, 51583, 
    51584, 51585, 51586
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00334

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "174.61"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "36.47"
      },
      "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": 36,
            "filtered": "13.23",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (51088,51087,22713,51086,51089,51090,51092,51093,22714,51091,51095,51103,22712,51105,51102,51101,51155,51154,51153,51152,21,51158,51157,51156,53,51160,51159,51297,51296,139,51300,51299,51298,51304,51303,51302,51301,138,51443,51309,51308,51307,51306,51305,129,51318,51317,51316,51,51319,51323,51322,51321,51320,50,51324,51497,51498,51564,51568,51569,51570,51571,51572,51573,51565,51574,51575,51576,51577,51566,51578,51579,51580,51581,51567,51582,51583,51584,51585,51586))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "3.65",
              "prefix_cost": "138.14",
              "data_read_per_join": "583"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
21 10,40M,43
50 23,40M,44
51 23,40M,43
53 10,40M,44
129 24,40M,42
138 24,40M,42
139 24,40M,42
22712 7M,40,42
22713 7M,40,42
22714 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
51101 7M,40,42
51102 7M,40,42
51103 7M,40,42
51105 7M,40,42
51152 10,40M,43
51153 10,40M,43
51154 10,40M,43
51155 10,40M,43
51156 10,40M,44
51157 10,40M,44
51158 10,40M,44
51159 10,40M,44
51160 10,40M,44
51296 24,40M,42
51297 24,40M,42
51298 24,40M,42
51299 24,40M,42
51300 24,40M,42
51301 24,40M,42
51302 24,40M,42
51303 24,40M,42
51304 24,40M,42
51305 24,40M,42
51306 24,40M,42
51307 24,40M,42
51308 24,40M,42
51309 24,40M,42
51316 23,40M,43
51317 23,40M,43
51318 23,40M,43
51319 23,40M,43
51320 23,40M,44
51321 23,40M,44
51322 23,40M,44
51323 23,40M,44
51324 23,40M,44
51443 24,40M,42
51497 33M,40
51498 33M,40
51564 9M,40,42
51565 10,40M,42
51566 9M,40,42
51567 10M,40,42
51568 9M,40,42
51569 9M,40,42
51570 9M,40,42
51571 9M,40,42
51572 9M,40,42
51573 9M,40,42
51574 10,40M,42
51575 10,40M,42
51576 10,40M,42
51577 10,40M,42
51578 9M,40,42
51579 9M,40,42
51580 9M,40,42
51581 9M,40,42
51582 10M,40,42
51583 10M,40,42
51584 10M,40,42
51585 10M,40,42
51586 10M,40,42