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 (
    10102, 
    10643, 
    10178, 
    10177, 
    10606, 
    10644, 
    10101, 
    10175, 
    10100, 
    15962, 
    15961, 
    15960, 
    15959, 
    10094, 
    10093, 
    9813, 
    10097, 
    9760, 
    9759, 
    9934, 
    9933, 
    9932, 
    9931, 
    9936, 
    9947, 
    9953, 
    9952, 
    10074, 
    10096, 
    10075, 
    10602, 
    10087, 
    10130, 
    10603, 
    10135, 
    10134, 
    10139, 
    10138, 
    10137, 
    10141, 
    10143, 
    10142, 
    12628, 
    12627, 
    12629, 
    10095, 
    10195, 
    10194, 
    10193, 
    10190, 
    10496, 
    10198, 
    10202, 
    10201, 
    10200, 
    10227, 
    10226, 
    10651, 
    9945, 
    10642, 
    10601, 
    9727, 
    9728, 
    9729, 
    9730, 
    9731, 
    9732, 
    9733, 
    10181, 
    9734, 
    9735, 
    9724, 
    10655, 
    10654, 
    10605, 
    10604, 
    9873, 
    10653, 
    10646, 
    10645, 
    10647, 
    10648, 
    10649, 
    10726, 
    10650, 
    9789, 
    10547, 
    9796, 
    9874, 
    9875, 
    9879, 
    9887, 
    9941, 
    9942, 
    9943, 
    9944, 
    10744, 
    10745, 
    10746, 
    10739, 
    10740, 
    10741, 
    10742, 
    10734, 
    10735, 
    10736, 
    10737, 
    10738, 
    10688, 
    10727, 
    10728, 
    10729, 
    10730, 
    10731, 
    10732, 
    10733, 
    10713, 
    10714, 
    10715, 
    10716, 
    10717, 
    10718, 
    10719, 
    10720, 
    10721, 
    10722, 
    10723, 
    10704
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00243

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": 143,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10102,10643,10178,10177,10606,10644,10101,10175,10100,15962,15961,15960,15959,10094,10093,9813,10097,9760,9759,9934,9933,9932,9931,9936,9947,9953,9952,10074,10096,10075,10602,10087,10130,10603,10135,10134,10139,10138,10137,10141,10143,10142,12628,12627,12629,10095,10195,10194,10193,10190,10496,10198,10202,10201,10200,10227,10226,10651,9945,10642,10601,9727,9728,9729,9730,9731,9732,9733,10181,9734,9735,9724,10655,10654,10605,10604,9873,10653,10646,10645,10647,10648,10649,10726,10650,9789,10547,9796,9874,9875,9879,9887,9941,9942,9943,9944,10744,10745,10746,10739,10740,10741,10742,10734,10735,10736,10737,10738,10688,10727,10728,10729,10730,10731,10732,10733,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10704)"
    },
    "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
9724 1671M
9727 1671M
9728 1671M
9729 1671M
9730 1671M
9731 1671M
9732 1671M
9733 1671M
9734 1671M
9735 1671M
9759 275M
9760 275M
9789 700M
9796 1672M
9813 1666M
9873 708M
9874 1672M
9875 1672M
9879 1672M
9887 1672M
9931 275M
9932 275M
9933 275M
9934 275M
9936 275M
9941 1672M
9942 1672M
9943 1672M
9944 1672M
9945 1671M
9947 275M
9952 275M
9953 275M
10074 275M
10075 275M
10087 275M
10093 1666M
10094 1666M
10095 1666M
10096 1666M
10097 1666M
10100 275M
10101 275M
10102 275M
10130 275M
10134 275M
10135 275M
10137 275M
10138 275M
10139 275M
10141 275M
10142 275M
10143 275M
10175 1666M
10177 1666M
10178 1666M
10181 1671M
10190 275M
10193 275M
10194 275M
10195 275M
10198 275M
10200 275M
10201 275M
10202 275M
10226 275M
10227 275M
10496 275M
10547 700M
10601 1671M
10602 275M
10603 275M
10604 708M
10605 708M
10606 1666M
10642 275M
10643 1666M
10644 1666M
10645 700M
10646 700M
10647 700M
10648 700M
10649 700M
10650 700M
10651 275M
10653 700M
10654 708M
10655 708M
10688 1664M
10704 1662M
10713 1663M
10714 1663M
10715 1663M
10716 1663M
10717 1663M
10718 1663M
10719 1663M
10720 1663M
10721 1663M
10722 1663M
10723 1663M
10726 700M
10727 1664M
10728 1664M
10729 1664M
10730 1664M
10731 1664M
10732 1664M
10733 1664M
10734 1665M
10735 1665M
10736 1665M
10737 1665M
10738 1665M
10739 1669M
10740 1669M
10741 1669M
10742 1669M
10744 1668M
10745 1668M
10746 1668M
12627 277,270,267M
12628 267,277,270M
12629 267,277,270M
15959 272M
15960 272M
15961 272M
15962 272M