SELECT 
  products_categories.product_id, 
  GROUP_CONCAT(
    IF(
      products_categories.link_type = "M", 
      CONCAT(
        products_categories.category_id, 
        "M"
      ), 
      products_categories.category_id
    )
  ) AS category_ids 
FROM 
  products_categories 
  INNER JOIN categories ON categories.category_id = products_categories.category_id 
  AND categories.storefront_id IN (0, 1) 
  AND (
    categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, categories.usergroup_ids) 
    OR FIND_IN_SET(1, categories.usergroup_ids)
  ) 
  AND categories.status IN ('A', 'H') 
WHERE 
  products_categories.product_id IN (
    212190, 213341, 204738, 213340, 204740, 
    311173, 315133, 323613, 410174, 327013, 
    468818, 317773, 370573, 423934, 439778, 
    382453, 407534, 328333, 363213, 426014, 
    374533, 392254, 338893, 409414, 439218, 
    367173, 462218, 360013, 368493, 474098, 
    369813, 414134, 474858, 390374, 410734, 
    421294, 488058, 324373, 373213, 451098, 
    414694, 429974, 495218, 442418, 357373, 
    443738, 311733, 312493
  ) 
GROUP BY 
  products_categories.product_id

Query time 0.00166

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "50.92"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "link_type",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 92,
            "rows_produced_per_join": 92,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "9.52",
              "eval_cost": "9.20",
              "prefix_cost": "18.72",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`goimagine`.`products_categories`.`product_id` in (212190,213341,204738,213340,204740,311173,315133,323613,410174,327013,468818,317773,370573,423934,439778,382453,407534,328333,363213,426014,374533,392254,338893,409414,439218,367173,462218,360013,368493,474098,369813,414134,474858,390374,410734,421294,488058,324373,373213,451098,414694,429974,495218,442418,357373,443738,311733,312493))"
          }
        },
        {
          "table": {
            "table_name": "categories",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "goimagine.products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "23.00",
              "eval_cost": "0.46",
              "prefix_cost": "50.92",
              "data_read_per_join": "12K"
            },
            "used_columns": [
              "category_id",
              "usergroup_ids",
              "status",
              "storefront_id"
            ],
            "attached_condition": "((`goimagine`.`categories`.`storefront_id` in (0,1)) and ((`goimagine`.`categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`goimagine`.`categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`goimagine`.`categories`.`usergroup_ids`))) and (`goimagine`.`categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
204738 85,3M
204740 387M
212190 387M
213340 387M
213341 387M
311173 85,3M
311733 85,3M
312493 85,3M
315133 85,3M
317773 85,3M
323613 85,3M
324373 85,3M
327013 85,3M
328333 85,3M
338893 85,3M
357373 85,3M
360013 85,3M
363213 85,3M
367173 85,3M
368493 85,3M
369813 85,3M
370573 85,3M
373213 85,3M
374533 85,3M
382453 85,3M
390374 85,3M
392254 85,3M
407534 85,3M
409414 85,3M
410174 85,3M
410734 85,3M
414134 85,3M
414694 85,3M
421294 85,3M
423934 85,3M
426014 85,3M
429974 85,3M
439218 85,3M
439778 85,3M
442418 85,3M
443738 85,3M
451098 85,3M
462218 85,3M
468818 85,3M
474098 85,3M
474858 85,3M
488058 85,3M
495218 85,3M