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 (
    284648, 282838, 282836, 278049, 275782, 
    272636, 269248, 266156, 265783, 266092, 
    265784, 258086, 252518, 257589, 256790, 
    256001, 252277, 242304, 239493, 237409, 
    235920, 231091, 227946, 224968, 226497, 
    225207, 225304, 225273, 204337, 201932, 
    197488, 196621, 196622, 201889, 192454, 
    193258, 192450, 108385, 144425, 175093, 
    167561, 167552, 161993, 167239, 166202, 
    165403, 165290, 164900, 164913, 161865, 
    164905, 162133, 164899, 160627, 156319, 
    132617, 139306, 120811, 139394, 132610, 
    139524, 131992, 127555, 126324, 126318, 
    126321, 108204, 114844, 120905, 108233, 
    120730, 108232, 120456, 108246, 108180
  ) 
GROUP BY 
  products_categories.product_id

Query time 0.00400

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "239.69"
    },
    "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": 435,
            "rows_produced_per_join": 435,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "43.94",
              "eval_cost": "43.50",
              "prefix_cost": "87.44",
              "data_read_per_join": "6K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`goimagine`.`products_categories`.`product_id` in (284648,282838,282836,278049,275782,272636,269248,266156,265783,266092,265784,258086,252518,257589,256790,256001,252277,242304,239493,237409,235920,231091,227946,224968,226497,225207,225304,225273,204337,201932,197488,196621,196622,201889,192454,193258,192450,108385,144425,175093,167561,167552,161993,167239,166202,165403,165290,164900,164913,161865,164905,162133,164899,160627,156319,132617,139306,120811,139394,132610,139524,131992,127555,126324,126318,126321,108204,114844,120905,108233,120730,108232,120456,108246,108180))"
          }
        },
        {
          "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": 21,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "108.75",
              "eval_cost": "2.18",
              "prefix_cost": "239.69",
              "data_read_per_join": "56K"
            },
            "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
108180 78,5843,6368,7695,8M
108204 100,5843,7695,10333,45M
108232 45,5843,6817,7695,10333,78M
108233 78,5843,7695,10333,4M
108246 266,5843,7405,7695,8695,10163,10334,5M
108385 112,5843,6365,78M
114844 2M
120456 2185,5843,6817,7405,7695,10334,10335,78M
120730 8,5843,6368,6369,6817,4M
120811 2M
120905 100,6483,6817,45M
126318 45,5843,6817,7695,4M
126321 278,5843,6817,7405,7695,10334,10335,10336,76M
126324 78,5843,6817,7695,14094,4M
127555 266,5843,6370,6817,7405,7695,8499,8694,8695,10163,10334,10335,4M
131992 47,5843,6817,7695,45M
132610 5843,6370,6817,6866,7651,7695,8499,109M
132617 78,5843,6817,7695,4M
139306 82,2M
139394 45,5843,6368,6369,6817,7695,8443,8498,4M
139524 5843,6366,6817,6866,7651,8443,8489,4M
144425 132,2M
156319 78,5843,6368,6369,6817,7695,4M
160627 109,5843,6370,6817,7221,7695,10482,87M
161865 87,5843,6370,6817,7221,7266,7695,8031,10482,78M
161993 195,6370,6817,7695,9148,10409,10482,4M
162133 78,6817,11350,19514,87M
164899 78,5843,6368,6369,6370,6817,7695,10482,4M
164900 78,5843,6369,6370,6817,7405,7695,8694,10482,4M
164905 82,2M
164913 100,5843,6370,6817,7695,10333,10482,45M
165290 82,2M
165403 78,5843,6817,7695,10853,14094,73M
166202 197,5843,6817,7695,45M
167239 73,5843,6817,7695,10853,4M
167552 169,5843,6370,6817,7266,7695,10482,15M
167561 45,5843,6369,6370,6817,7405,7695,8694,10482,271M
175093 191,5843,6817,7695,45M
192450 87,5843,6817,7221,7695,7713,78M
192454 195,5843,6370,6817,7405,7695,8694,9148,5M
193258 90,5843,6817,7695,78M
196621 2M
196622 2M
197488 82,2M
201889 78,5843,6817,7266,7695,13321,13628,81M
201932 82,2M
204337 51,5843,6817,7695,45M
224968 187,5843,6817,7695,199M
225207 191,5843,6817,7695,15466,45M
225273 191,5843,6817,7695,15466,45M
225304 191,5843,6817,7695,15466,45M
226497 191,5843,6817,7695,15466,45M
227946 48,45M
231091 4,5843,6817,7695,9148,16173,195M
235920 45,5843,6817,7695,9148,18857,195M
237409 196,6817,7695,9148,18878,195M
239493 196,6817,7695,9148,18878,195M
242304 196,6817,7695,9148,18886,195M
252277 4,6370,6817,7695,9148,18880,195M
252518 6817,7695,9148,18878,195M
256001 6370,6817,7695,9148,18878,195M
256790 6817,7695,9148,18880,195M
257589 4,6817,7695,9148,18878,195M
258086 4,6817,7695,9148,18879,195M
265783 78,6817,7695,11350,19509,6667M
265784 78,6817,7695,19515,6667M
266092 78,6817,7405,7695,8499,10334,10335,19515,6667M
266156 78,6817,7695,11350,19509,6667M
269248 6738,6817,7695,19515,19803,6667M
272636 6817,7695,10163,20302,257M
275782 131,6817,7266,7695,20143,170M
278049 131,6817,7266,7695,20611,170M
282836 18,2M
282838 2,6817,7266,7695,20955,147M
284648 2,6817,7266,7695,147M