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 (
    10331, 
    10358, 
    10378, 
    10311, 
    10332, 
    10359, 
    10379, 
    10312, 
    10333, 
    10360, 
    10380, 
    10334, 
    10361, 
    10381, 
    10335, 
    10362, 
    10336, 
    10363, 
    10337, 
    10364, 
    10338, 
    10365, 
    10339, 
    10366, 
    10340, 
    10367, 
    10341, 
    10368, 
    10342, 
    12565, 
    10293, 
    10296, 
    10301, 
    10313, 
    10318, 
    10294, 
    10297, 
    10302, 
    10314, 
    10319, 
    12552, 
    12554, 
    10295, 
    10298, 
    10303, 
    10315, 
    10320, 
    10299, 
    10304, 
    10316, 
    10321, 
    10300, 
    10317, 
    10322, 
    12572, 
    12575, 
    12571, 
    12573, 
    10273, 
    10272, 
    10271, 
    10270, 
    10269, 
    10268, 
    10267, 
    10266, 
    10265, 
    10264, 
    10263, 
    12570, 
    12567, 
    12569, 
    12568, 
    10261, 
    10260, 
    10259, 
    10258, 
    10257, 
    10256, 
    10255, 
    10254, 
    10253, 
    10252, 
    10251, 
    10250, 
    10249, 
    10248, 
    10247, 
    10246, 
    10245, 
    10244, 
    10243, 
    10242, 
    10241, 
    10203, 
    10204, 
    10205, 
    10206, 
    10207, 
    10208, 
    10209, 
    10210, 
    10211, 
    10212, 
    10213, 
    10214, 
    10215, 
    10216, 
    10217, 
    10218, 
    10219, 
    10220, 
    10221, 
    10222, 
    10144, 
    10145, 
    10146, 
    10147, 
    10148, 
    10149, 
    10150, 
    10151, 
    10152, 
    12969, 
    10103, 
    10104, 
    10105, 
    10106
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00331

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_products_categories",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 139,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10331,10358,10378,10311,10332,10359,10379,10312,10333,10360,10380,10334,10361,10381,10335,10362,10336,10363,10337,10364,10338,10365,10339,10366,10340,10367,10341,10368,10342,12565,10293,10296,10301,10313,10318,10294,10297,10302,10314,10319,12552,12554,10295,10298,10303,10315,10320,10299,10304,10316,10321,10300,10317,10322,12572,12575,12571,12573,10273,10272,10271,10270,10269,10268,10267,10266,10265,10264,10263,12570,12567,12569,12568,10261,10260,10259,10258,10257,10256,10255,10254,10253,10252,10251,10250,10249,10248,10247,10246,10245,10244,10243,10242,10241,10203,10204,10205,10206,10207,10208,10209,10210,10211,10212,10213,10214,10215,10216,10217,10218,10219,10220,10221,10222,10144,10145,10146,10147,10148,10149,10150,10151,10152,12969,10103,10104,10105,10106)"
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["noframes_angrybeaver.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "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')"
    }
  }
}

Result

product_id category_ids
10103 1618M
10104 1618M
10105 1618M
10106 1618M
10144 1151M
10145 1151M
10146 1151M
10147 1151M
10148 1151M
10149 1151M
10150 1151M
10151 1151M
10152 1151M
10203 1637M
10204 1637M
10205 1637M
10206 1637M
10207 1637M
10208 1637M
10209 1637M
10210 1637M
10211 1637M
10212 1637M
10213 1637M
10214 1637M
10215 1637M
10216 1637M
10217 1637M
10218 1637M
10219 1637M
10220 1637M
10221 1637M
10222 1637M
10241 1622M
10242 1622M
10243 1622M
10244 1622M
10245 1622M
10246 1622M
10247 1622M
10248 1622M
10249 1622M
10250 1622M
10251 1622M
10252 1622M
10253 1622M
10254 1622M
10255 1622M
10256 1622M
10257 1622M
10258 1622M
10259 1622M
10260 1622M
10261 1622M
10263 323M
10264 323M
10265 323M
10266 323M
10267 323M
10268 323M
10269 323M
10270 323M
10271 323M
10272 323M
10273 323M
10293 775M
10294 775M
10295 775M
10296 1645M
10297 1645M
10298 1645M
10299 1645M
10300 1645M
10301 1644M
10302 1644M
10303 1644M
10304 1644M
10311 1647M
10312 1647M
10313 1643M
10314 1643M
10315 1643M
10316 1643M
10317 1643M
10318 1643M
10319 1643M
10320 1643M
10321 1643M
10322 1643M
10331 1648M
10332 1648M
10333 1648M
10334 1648M
10335 1648M
10336 1648M
10337 1648M
10338 1648M
10339 1648M
10340 1648M
10341 1648M
10342 1648M
10358 1646M
10359 1646M
10360 1646M
10361 1646M
10362 1646M
10363 1646M
10364 1646M
10365 1646M
10366 1646M
10367 1646M
10368 1646M
10378 1652M
10379 1652M
10380 1652M
10381 1652M
12552 267,270,268M
12554 267,270,268M
12565 278,277,267M
12567 278,267,277M
12568 267,278,277M
12569 278,277,267M
12570 277,267,278M
12571 267,278,277M
12572 267,277,278M
12573 267,278,277M
12575 267,278,277M
12969 301,1151M