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 (
    10456, 
    10455, 
    12702, 
    10446, 
    12579, 
    12580, 
    12700, 
    10444, 
    10443, 
    10442, 
    10441, 
    10440, 
    10439, 
    10438, 
    10437, 
    10436, 
    10435, 
    10434, 
    10433, 
    10432, 
    10431, 
    10430, 
    10429, 
    10428, 
    10427, 
    10426, 
    10425, 
    12564, 
    10424, 
    10423, 
    10422, 
    10421, 
    10420, 
    10419, 
    10418, 
    10417, 
    12856, 
    10416, 
    10415, 
    10414, 
    12855, 
    12854, 
    12853, 
    10412, 
    10411, 
    10410, 
    10409, 
    10408, 
    10407, 
    10406, 
    15781, 
    10405, 
    10404, 
    10403, 
    10402, 
    10401, 
    10400, 
    10399, 
    10398, 
    10397, 
    10395, 
    10394, 
    10393, 
    10392, 
    10390, 
    10389, 
    10388, 
    10387, 
    10386, 
    10384, 
    10383, 
    10382, 
    12679, 
    12678, 
    12677, 
    12676, 
    12675, 
    12674, 
    12673, 
    15779, 
    12672, 
    12671, 
    12994, 
    12993, 
    12992, 
    12991, 
    12990, 
    12989, 
    12988, 
    12987, 
    12986, 
    12985, 
    10343, 
    10345, 
    10346, 
    10347, 
    10348, 
    10349, 
    10323, 
    10350, 
    10370, 
    10324, 
    10351, 
    10371, 
    10325, 
    10352, 
    10372, 
    10305, 
    10326, 
    10353, 
    10373, 
    10306, 
    10327, 
    10354, 
    10374, 
    10307, 
    10328, 
    10355, 
    10375, 
    10308, 
    10329, 
    10356, 
    10376, 
    10309, 
    10330, 
    10357, 
    10377, 
    10310
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00481

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": 137,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10456,10455,12702,10446,12579,12580,12700,10444,10443,10442,10441,10440,10439,10438,10437,10436,10435,10434,10433,10432,10431,10430,10429,10428,10427,10426,10425,12564,10424,10423,10422,10421,10420,10419,10418,10417,12856,10416,10415,10414,12855,12854,12853,10412,10411,10410,10409,10408,10407,10406,15781,10405,10404,10403,10402,10401,10400,10399,10398,10397,10395,10394,10393,10392,10390,10389,10388,10387,10386,10384,10383,10382,12679,12678,12677,12676,12675,12674,12673,15779,12672,12671,12994,12993,12992,12991,12990,12989,12988,12987,12986,12985,10343,10345,10346,10347,10348,10349,10323,10350,10370,10324,10351,10371,10325,10352,10372,10305,10326,10353,10373,10306,10327,10354,10374,10307,10328,10355,10375,10308,10329,10356,10376,10309,10330,10357,10377,10310)"
    },
    "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
10305 1647M
10306 1647M
10307 1647M
10308 1647M
10309 1647M
10310 1647M
10323 1648M
10324 1648M
10325 1648M
10326 1648M
10327 1648M
10328 1648M
10329 1648M
10330 1648M
10343 1646M
10345 1646M
10346 1646M
10347 1646M
10348 1646M
10349 1646M
10350 1646M
10351 1646M
10352 1646M
10353 1646M
10354 1646M
10355 1646M
10356 1646M
10357 1646M
10370 1652M
10371 1652M
10372 1652M
10373 1652M
10374 1652M
10375 1652M
10376 1652M
10377 1652M
10382 1654M
10383 1654M
10384 1654M
10386 1650M
10387 1650M
10388 1650M
10389 1650M
10390 1650M
10392 1650M
10393 1650M
10394 1650M
10395 1650M
10397 787M
10398 787M
10399 787M
10400 787M
10401 787M
10402 787M
10403 1655M
10404 1655M
10405 1655M
10406 1655M
10407 1655M
10408 1655M
10409 1655M
10410 1655M
10411 1655M
10412 1655M
10414 1655M
10415 1655M
10416 1655M
10417 1655M
10418 1655M
10419 1655M
10420 1655M
10421 1655M
10422 1655M
10423 1655M
10424 1655M
10425 1651M
10426 1651M
10427 1651M
10428 1651M
10429 1651M
10430 1651M
10431 1651M
10432 1651M
10433 1651M
10434 1651M
10435 1651M
10436 1651M
10437 1651M
10438 1651M
10439 1651M
10440 1651M
10441 1651M
10442 1651M
10443 1651M
10444 1651M
10446 1645M
10455 1656M
10456 1656M
12564 277,278,267M
12579 277,278,267M
12580 278,277,267M
12671 784M
12672 784M
12673 784M
12674 784M
12675 784M
12676 784M
12677 784M
12678 784M
12679 784M
12700 787M
12702 323M
12853 1655M
12854 1655M
12855 1655M
12856 1655M
12985 1649M
12986 1649M
12987 1649M
12988 1649M
12989 1649M
12990 1649M
12991 1649M
12992 1649M
12993 1649M
12994 1649M
15779 784M
15781 1655M