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 (
    13101, 
    12498, 
    12496, 
    12510, 
    12519, 
    12516, 
    12530, 
    12526, 
    12531, 
    12509, 
    12517, 
    12523, 
    12532, 
    12525, 
    12515, 
    12529, 
    12503, 
    13100, 
    12507, 
    12522, 
    12508, 
    12506, 
    12500, 
    12499, 
    12513, 
    12514, 
    13099, 
    12521, 
    12527, 
    12511, 
    12512, 
    12528, 
    12520, 
    12497, 
    12505, 
    12502, 
    12504, 
    10847, 
    13152, 
    10846, 
    10845, 
    10844, 
    10843, 
    10842, 
    10841, 
    10840, 
    10839, 
    10838, 
    10837, 
    10836, 
    10835, 
    10834, 
    10833, 
    10832, 
    10831, 
    10830, 
    10829, 
    10828, 
    10827, 
    12642, 
    12648, 
    12651, 
    12645, 
    12644, 
    12650, 
    12649, 
    12643, 
    12640, 
    12647, 
    15800, 
    12639, 
    12641, 
    12638, 
    12637, 
    12634, 
    12635, 
    12636, 
    12654, 
    12653, 
    12652, 
    12656, 
    12655, 
    12646, 
    12631, 
    12998, 
    12997, 
    12996, 
    12995, 
    10826, 
    10825, 
    10824, 
    10823, 
    10822, 
    10821, 
    10820, 
    10819, 
    10818, 
    10817, 
    10816, 
    10815, 
    10814, 
    10813, 
    13044, 
    13043, 
    13042, 
    13041, 
    13040, 
    13039, 
    13038, 
    13037, 
    13036, 
    13035, 
    13034, 
    13033, 
    13032, 
    13031, 
    13030, 
    13029, 
    13028, 
    13027, 
    13026, 
    13025, 
    13024, 
    13023, 
    13022, 
    13021, 
    13020, 
    13019
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00204

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": 167,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (13101,12498,12496,12510,12519,12516,12530,12526,12531,12509,12517,12523,12532,12525,12515,12529,12503,13100,12507,12522,12508,12506,12500,12499,12513,12514,13099,12521,12527,12511,12512,12528,12520,12497,12505,12502,12504,10847,13152,10846,10845,10844,10843,10842,10841,10840,10839,10838,10837,10836,10835,10834,10833,10832,10831,10830,10829,10828,10827,12642,12648,12651,12645,12644,12650,12649,12643,12640,12647,15800,12639,12641,12638,12637,12634,12635,12636,12654,12653,12652,12656,12655,12646,12631,12998,12997,12996,12995,10826,10825,10824,10823,10822,10821,10820,10819,10818,10817,10816,10815,10814,10813,13044,13043,13042,13041,13040,13039,13038,13037,13036,13035,13034,13033,13032,13031,13030,13029,13028,13027,13026,13025,13024,13023,13022,13021,13020,13019)"
    },
    "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
10813 1672M
10814 1672M
10815 1672M
10816 1672M
10817 1672M
10818 1672M
10819 1672M
10820 1672M
10821 1672M
10822 1672M
10823 1672M
10824 1672M
10825 1672M
10826 1672M
10827 1676M
10828 1676M
10829 1685M
10830 1685M
10831 1685M
10832 1685M
10833 1685M
10834 1685M
10835 1685M
10836 1685M
10837 1685M
10838 1685M
10839 1685M
10840 667M
10841 667M
10842 667M
10843 667M
10844 667M
10845 667M
10846 667M
10847 667M
12496 275M
12497 275M
12498 275M
12499 275M
12500 275M
12502 275M
12503 275M
12504 275M
12505 275M
12506 275M
12507 275M
12508 275M
12509 275M
12510 275M
12511 275M
12512 275M
12513 275M
12514 275M
12515 275M
12516 275M
12517 275M
12519 275M
12520 275M
12521 275M
12522 275M
12523 275M
12525 275M
12526 275M
12527 275M
12528 275M
12529 275M
12530 275M
12531 275M
12532 275M
12631 278,277,267M
12634 267,270,268M
12635 267,268,270M
12636 268,267,270M
12637 277,267,270M
12638 267,277,285,270M
12639 270,285,277,267M
12640 270,277,285,267M
12641 277,270,285,267M
12642 277,267,285,270M
12643 267,270,285,277M
12644 267,277,285,270M
12645 285,270,277,267M
12646 268,270,267M
12647 277,270,285,267M
12648 267,285,277,270M
12649 277,270,285,267M
12650 270,277,285,267M
12651 270,277,285,267M
12652 270,268,267M
12653 268,270,267M
12654 268,267,270M
12655 270,268,267M
12656 270,268,267M
12995 1684,301M
12996 1684,301M
12997 1684,301M
12998 1684,301M
13019 301,1683M
13020 301,1683M
13021 301,1683M
13022 301,1683M
13023 301,1683M
13024 301,1683M
13025 301,1683M
13026 301,1683M
13027 301,1683M
13028 1683,301M
13029 301,1683M
13030 301,1683M
13031 313,301M
13032 313,301M
13033 313,301M
13034 313,301M
13035 313,301M
13036 313,301M
13037 313,301M
13038 313,301M
13039 313,301M
13040 313,301M
13041 313,301M
13042 313,301M
13043 313,301M
13044 313,301M
13099 275M
13100 275M
13101 275M
13152 667M
15800 285,270,267,277M