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 (
    130681, 145323, 114897, 131992, 165403, 
    257589, 115093, 108241, 197488, 175093, 
    165284, 156319, 202170, 167561, 108245, 
    139394, 159546, 156799, 211705, 252518, 
    108234, 164905, 108385, 112103, 129512, 
    161993, 112102, 129527, 144425, 131847, 
    108248, 120905, 108242, 242304, 111949, 
    196622, 111951, 133995, 128503, 131987, 
    156326, 114827, 132626, 135126, 138125, 
    161899, 114282, 174366
  ) 
GROUP BY 
  products_categories.product_id

Query time 0.00295

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "140.61"
    },
    "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": 255,
            "rows_produced_per_join": 255,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "25.86",
              "eval_cost": "25.50",
              "prefix_cost": "51.36",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`goimagine`.`products_categories`.`product_id` in (130681,145323,114897,131992,165403,257589,115093,108241,197488,175093,165284,156319,202170,167561,108245,139394,159546,156799,211705,252518,108234,164905,108385,112103,129512,161993,112102,129527,144425,131847,108248,120905,108242,242304,111949,196622,111951,133995,128503,131987,156326,114827,132626,135126,138125,161899,114282,174366))"
          }
        },
        {
          "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": 12,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "63.75",
              "eval_cost": "1.28",
              "prefix_cost": "140.61",
              "data_read_per_join": "33K"
            },
            "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
108234 78,5843,6368,6369,4M
108241 78,5843,6365,7695,4M
108242 87,5843,6365,6370,7221,7712,78M
108245 2566,5843,6365,78M
108248 5843,6366,7651,7695,8488,4M
108385 112,5843,6365,78M
111949 100,6247,45M
111951 100,6247,45M
112102 100,6247,45M
112103 100,6247,7695,45M
114282 109,5843,6365,78M
114827 2M
114897 78,6247,4M
115093 78,5843,6365,71M
120905 100,6483,6817,45M
128503 78,5843,6370,6817,7695,4M
129512 272,5843,6369,6370,6817,7695,191M
129527 186,5843,6817,45M
130681 109,5843,6370,6817,7695,8499,4M
131847 78,5843,6817,7221,7713,87M
131987 78,5843,6817,7221,7713,87M
131992 47,5843,6817,7695,45M
132626 78,5843,6817,7221,7713,87M
133995 2,5843,6817,7266,7695,7905,17M
135126 82,2M
138125 5843,6366,7651,8488,4M
139394 45,5843,6368,6369,6817,7695,8443,8498,4M
144425 132,2M
145323 194,5843,6817,7695,8443,8498,317M
156319 78,5843,6368,6369,6817,7695,4M
156326 260,5843,6817,7695,10163,179M
156799 45,5843,6370,6817,7695,8499,10161,109M
159546 195,6370,6817,7695,8499,9148,10409,4M
161899 186,5843,6817,7695,78M
161993 195,6370,6817,7695,9148,10409,10482,4M
164905 82,2M
165284 305,5843,6817,7695,10482,78M
165403 78,5843,6817,7695,10853,14094,73M
167561 45,5843,6369,6370,6817,7405,7695,8694,10482,271M
174366 82,5843,6817,7221,7695,13321,13356,14094,87M
175093 191,5843,6817,7695,45M
196622 2M
197488 82,2M
202170 319,5843,6817,7695,13579,78M
211705 326,5843,6817,7266,7695,7905,8031,14094,82M
242304 196,6817,7695,9148,18886,195M
252518 6817,7695,9148,18878,195M
257589 4,6817,7695,9148,18878,195M