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 (
    10705, 
    10706, 
    10707, 
    10708, 
    10709, 
    10710, 
    10711, 
    10712, 
    10668, 
    10669, 
    10670, 
    10671, 
    10672, 
    10673, 
    10674, 
    10675, 
    10676, 
    10677, 
    10678, 
    10679, 
    10680, 
    10681, 
    10682, 
    10683, 
    10684, 
    10685, 
    10686, 
    10725, 
    10724, 
    10701, 
    10699, 
    10698, 
    10696, 
    10695, 
    10693, 
    10692, 
    15958, 
    15957, 
    15956, 
    12600, 
    12602, 
    12604, 
    12606, 
    12608, 
    12603, 
    12615, 
    12597, 
    12609, 
    12610, 
    12607, 
    12596, 
    12611, 
    12613, 
    12612, 
    12605, 
    12601, 
    12616, 
    12599, 
    10666, 
    10664, 
    10663, 
    10661, 
    10659, 
    10658, 
    10657, 
    12619, 
    12620, 
    12593, 
    12595, 
    12585, 
    12587, 
    12623, 
    13091, 
    12588, 
    12590, 
    12591, 
    12592, 
    10497, 
    10498, 
    10499, 
    10500, 
    10501, 
    10502, 
    10503, 
    10504, 
    10505, 
    10506, 
    10507, 
    10508, 
    10509, 
    10510, 
    10511, 
    10512, 
    10513, 
    10514, 
    10515, 
    10516, 
    10517, 
    10518, 
    10519, 
    10520, 
    10521, 
    10641, 
    10640, 
    10639, 
    10638, 
    10637, 
    10636, 
    10635, 
    10634, 
    10633, 
    10632, 
    10631, 
    10630, 
    10629, 
    10628, 
    10627, 
    10626, 
    10625, 
    10624, 
    10623, 
    10622, 
    10621, 
    10620, 
    10619, 
    10618, 
    10617, 
    10616
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00263

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": 168,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10705,10706,10707,10708,10709,10710,10711,10712,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10725,10724,10701,10699,10698,10696,10695,10693,10692,15958,15957,15956,12600,12602,12604,12606,12608,12603,12615,12597,12609,12610,12607,12596,12611,12613,12612,12605,12601,12616,12599,10666,10664,10663,10661,10659,10658,10657,12619,12620,12593,12595,12585,12587,12623,13091,12588,12590,12591,12592,10497,10498,10499,10500,10501,10502,10503,10504,10505,10506,10507,10508,10509,10510,10511,10512,10513,10514,10515,10516,10517,10518,10519,10520,10521,10641,10640,10639,10638,10637,10636,10635,10634,10633,10632,10631,10630,10629,10628,10627,10626,10625,10624,10623,10622,10621,10620,10619,10618,10617,10616)"
    },
    "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
10497 455M
10498 455M
10499 455M
10500 455M
10501 455M
10502 455M
10503 455M
10504 455M
10505 455M
10506 455M
10507 455M
10508 455M
10509 455M
10510 455M
10511 455M
10512 455M
10513 455M
10514 455M
10515 455M
10516 455M
10517 455M
10518 455M
10519 455M
10520 455M
10521 455M
10616 1659M
10617 1659M
10618 1659M
10619 1659M
10620 1659M
10621 1659M
10622 1659M
10623 1659M
10624 1659M
10625 1660M
10626 1660M
10627 1660M
10628 1660M
10629 1660M
10630 1660M
10631 1660M
10632 1660M
10633 1660M
10634 1660M
10635 1660M
10636 1660M
10637 1650M
10638 1650M
10639 1650M
10640 1650M
10641 1650M
10657 275M
10658 275M
10659 275M
10661 275M
10663 275M
10664 275M
10666 275M
10668 1661M
10669 1661M
10670 1661M
10671 1661M
10672 1661M
10673 1661M
10674 1661M
10675 1661M
10676 1661M
10677 1661M
10678 1661M
10679 1661M
10680 1661M
10681 1661M
10682 1661M
10683 1661M
10684 1661M
10685 1661M
10686 1661M
10692 275M
10693 275M
10695 275M
10696 275M
10698 275M
10699 275M
10701 275M
10705 1662M
10706 1662M
10707 1662M
10708 1662M
10709 1662M
10710 1662M
10711 1662M
10712 1662M
10724 775M
10725 775M
12585 270,267,1688M
12587 270,267,1688M
12588 285,270,267M
12590 267,278,277M
12591 277,267,278M
12592 267,277,278M
12593 270,267,1688M
12595 270,267,1688M
12596 277,267,270M
12597 277,267,270M
12599 277,267,270M
12600 277,267,270M
12601 267,270,277M
12602 270,277,267M
12603 267,277,270M
12604 267,270,277M
12605 267,277,270M
12606 277,270,267M
12607 270,267,277M
12608 270,277,267M
12609 270,267,277M
12610 270,277,267M
12611 270,267,277M
12612 270,267,277M
12613 270,267,277M
12615 270,267,277M
12616 277,270,267M
12619 267,270,1688M
12620 270,1688,267M
12623 267,1688,270M
13091 285,270,267M
15956 272M
15957 272M
15958 272M