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 (
    95114, 99478, 136598, 98587, 93064, 
    90460, 99480, 91782, 92017, 91783, 
    91777, 95110, 95716, 92365, 131318, 
    131312, 98586, 104674, 93059, 104677, 
    98584, 92030, 127795, 93062, 92461, 
    92031, 92465, 91357, 92462, 92367, 
    93063, 95702, 93065, 97069, 93066, 
    92464, 123544, 93058, 97078, 189132, 
    104676, 91356, 96730, 97156, 123546, 
    104675, 92463, 95107, 131316, 131320, 
    95072, 93054, 104673, 127897, 97071, 
    136415, 93061
  ) 
GROUP BY 
  products_categories.product_id

Query time 0.00163

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "31.65"
    },
    "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": 57,
            "rows_produced_per_join": 57,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "6.00",
              "eval_cost": "5.70",
              "prefix_cost": "11.70",
              "data_read_per_join": "912"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`goimagine`.`products_categories`.`product_id` in (95114,99478,136598,98587,93064,90460,99480,91782,92017,91783,91777,95110,95716,92365,131318,131312,98586,104674,93059,104677,98584,92030,127795,93062,92461,92031,92465,91357,92462,92367,93063,95702,93065,97069,93066,92464,123544,93058,97078,189132,104676,91356,96730,97156,123546,104675,92463,95107,131316,131320,95072,93054,104673,127897,97071,136415,93061))"
          }
        },
        {
          "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": 2,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "14.25",
              "eval_cost": "0.29",
              "prefix_cost": "31.65",
              "data_read_per_join": "7K"
            },
            "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
90460 118M
91356 183M
91357 183M
91777 183M
91782 183M
91783 183M
92017 183M
92030 183M
92031 183M
92365 183M
92367 183M
92461 183M
92462 183M
92463 183M
92464 183M
92465 183M
93054 183M
93058 183M
93059 183M
93061 183M
93062 183M
93063 183M
93064 183M
93065 183M
93066 183M
95072 183M
95107 183M
95110 183M
95114 183M
95702 183M
95716 183M
96730 183M
97069 183M
97071 183M
97078 183M
97156 183M
98584 183M
98586 183M
98587 183M
99478 183M
99480 183M
104673 183M
104674 183M
104675 183M
104676 183M
104677 183M
123544 183M
123546 183M
127795 183M
127897 183M
131312 183M
131316 183M
131318 183M
131320 183M
136415 183M
136598 183M
189132 6534M