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 (
    10615, 
    10614, 
    10613, 
    10612, 
    10611, 
    10610, 
    10609, 
    10608, 
    10607, 
    10600, 
    10599, 
    10598, 
    10597, 
    10596, 
    15952, 
    15951, 
    15950, 
    15949, 
    15947, 
    15946, 
    15945, 
    15944, 
    10583, 
    10582, 
    10581, 
    10580, 
    10579, 
    10578, 
    10577, 
    10576, 
    10575, 
    10573, 
    10572, 
    10569, 
    10568, 
    10567, 
    10566, 
    10565, 
    10563, 
    10561, 
    10560, 
    10558, 
    10557, 
    10556, 
    10552, 
    10551, 
    10550, 
    15943, 
    10545, 
    10544, 
    10543, 
    10542, 
    10541, 
    10540, 
    10539, 
    10538, 
    10537, 
    10536, 
    10535, 
    10534, 
    10533, 
    10532, 
    10531, 
    10530, 
    10529, 
    10528, 
    10527, 
    10526, 
    10525, 
    10524, 
    10522, 
    10495, 
    10494, 
    10493, 
    10492, 
    10491, 
    10490, 
    10489, 
    10488, 
    10487, 
    10486, 
    10485, 
    10483, 
    10482, 
    10481, 
    10480, 
    10479, 
    10478, 
    10477, 
    10476, 
    10475, 
    10474, 
    10473, 
    10472, 
    10471, 
    10470, 
    10469, 
    10468, 
    10467, 
    10466, 
    12582, 
    12583, 
    12584, 
    10465, 
    10464, 
    10463, 
    10462, 
    10461, 
    10460, 
    10459, 
    12581, 
    12699, 
    12698, 
    12697, 
    12696, 
    12695, 
    12694, 
    12693, 
    12692, 
    12691, 
    10447, 
    10448, 
    10449, 
    10450, 
    10451, 
    10452, 
    10453, 
    10454
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00167

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": 159,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10615,10614,10613,10612,10611,10610,10609,10608,10607,10600,10599,10598,10597,10596,15952,15951,15950,15949,15947,15946,15945,15944,10583,10582,10581,10580,10579,10578,10577,10576,10575,10573,10572,10569,10568,10567,10566,10565,10563,10561,10560,10558,10557,10556,10552,10551,10550,15943,10545,10544,10543,10542,10541,10540,10539,10538,10537,10536,10535,10534,10533,10532,10531,10530,10529,10528,10527,10526,10525,10524,10522,10495,10494,10493,10492,10491,10490,10489,10488,10487,10486,10485,10483,10482,10481,10480,10479,10478,10477,10476,10475,10474,10473,10472,10471,10470,10469,10468,10467,10466,12582,12583,12584,10465,10464,10463,10462,10461,10460,10459,12581,12699,12698,12697,12696,12695,12694,12693,12692,12691,10447,10448,10449,10450,10451,10452,10453,10454)"
    },
    "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
10447 1656M
10448 1656M
10449 1656M
10450 1656M
10451 1656M
10452 1656M
10453 1656M
10454 1656M
10459 1658M
10460 1658M
10461 1658M
10462 1658M
10463 1658M
10464 1658M
10465 1658M
10466 327M
10467 327M
10468 327M
10469 327M
10470 327M
10471 327M
10472 327M
10473 327M
10474 327M
10475 327M
10476 327M
10477 327M
10478 327M
10479 327M
10480 327M
10481 327M
10482 327M
10483 327M
10485 327M
10486 327M
10487 327M
10488 327M
10489 327M
10490 327M
10491 327M
10492 327M
10493 327M
10494 327M
10495 327M
10522 1651M
10524 1657M
10525 1657M
10526 1657M
10527 1657M
10528 1657M
10529 1657M
10530 1657M
10531 1657M
10532 1657M
10533 1657M
10534 1657M
10535 1657M
10536 1657M
10537 1657M
10538 1657M
10539 1657M
10540 1657M
10541 1657M
10542 1657M
10543 1657M
10544 1657M
10545 275M
10550 275M
10551 275M
10552 275M
10556 275M
10557 275M
10558 275M
10560 275M
10561 275M
10563 275M
10565 275M
10566 275M
10567 275M
10568 275M
10569 275M
10572 275M
10573 275M
10575 275M
10576 275M
10577 275M
10578 275M
10579 275M
10580 275M
10581 275M
10582 275M
10583 275M
10596 455M
10597 455M
10598 455M
10599 455M
10600 455M
10607 1659M
10608 1659M
10609 1659M
10610 1659M
10611 1659M
10612 1659M
10613 1659M
10614 1659M
10615 1659M
12581 277,267,278M
12582 267,277,278M
12583 267,277,278M
12584 278,267,277M
12691 1656M
12692 1656M
12693 1656M
12694 1656M
12695 1656M
12696 1656M
12697 1656M
12698 1656M
12699 1656M
15943 272M
15944 272M
15945 272M
15946 272M
15947 272M
15949 272M
15950 272M
15951 272M
15952 272M