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, 
  product_position_source.position AS position 
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') 
  LEFT JOIN cscart_products_categories AS product_position_source ON cscart_products_categories.product_id = product_position_source.product_id 
  AND product_position_source.category_id = 265 
WHERE 
  cscart_products_categories.product_id IN (
    10361, 
    10362, 
    10363, 
    10364, 
    10365, 
    10366, 
    10367, 
    10368, 
    10704, 
    10705, 
    10706, 
    10707, 
    10708, 
    10709, 
    10710, 
    10711, 
    10712, 
    10301, 
    10302, 
    10303, 
    10304, 
    10425, 
    10426, 
    10427, 
    10428, 
    10429, 
    10430, 
    10431, 
    10432, 
    10433, 
    10434, 
    10435, 
    10436, 
    10437, 
    10438, 
    10439, 
    10440, 
    10441, 
    10442, 
    10443, 
    10444, 
    10522, 
    9945, 
    10601, 
    9727, 
    9728, 
    9729, 
    9730, 
    9731, 
    9732, 
    9733, 
    10181, 
    9734, 
    9735, 
    9724, 
    9803, 
    9805, 
    9797, 
    9800, 
    9799, 
    9798, 
    9801, 
    9802, 
    9806, 
    9804, 
    9809, 
    9810, 
    9811, 
    9812, 
    9807, 
    9808, 
    9683, 
    10455, 
    10456, 
    10454, 
    10453, 
    10452, 
    10451, 
    10450, 
    10449, 
    10448, 
    10447, 
    12691, 
    12692, 
    12693, 
    12694, 
    12695, 
    12696, 
    12697, 
    12698, 
    12699, 
    10370, 
    10372, 
    10374, 
    10376, 
    10377, 
    10378, 
    10379, 
    10380, 
    10381, 
    10371, 
    10373, 
    10375, 
    10387, 
    10395, 
    10390, 
    10394, 
    10393, 
    10392, 
    10386, 
    10389, 
    10388, 
    10637, 
    10638, 
    10639, 
    10640, 
    10641, 
    10744, 
    10745, 
    10746, 
    9905, 
    10168, 
    10041, 
    10643, 
    10606, 
    10094, 
    10097, 
    12703
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00127

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": 128,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10361,10362,10363,10364,10365,10366,10367,10368,10704,10705,10706,10707,10708,10709,10710,10711,10712,10301,10302,10303,10304,10425,10426,10427,10428,10429,10430,10431,10432,10433,10434,10435,10436,10437,10438,10439,10440,10441,10442,10443,10444,10522,9945,10601,9727,9728,9729,9730,9731,9732,9733,10181,9734,9735,9724,9803,9805,9797,9800,9799,9798,9801,9802,9806,9804,9809,9810,9811,9812,9807,9808,9683,10455,10456,10454,10453,10452,10451,10450,10449,10448,10447,12691,12692,12693,12694,12695,12696,12697,12698,12699,10370,10372,10374,10376,10377,10378,10379,10380,10381,10371,10373,10375,10387,10395,10390,10394,10393,10392,10386,10389,10388,10637,10638,10639,10640,10641,10744,10745,10746,9905,10168,10041,10643,10606,10094,10097,12703)"
    },
    "table": {
      "table_name": "product_position_source",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "PRIMARY",
      "key_length": "6",
      "used_key_parts": ["category_id", "product_id"],
      "ref": [
        "const",
        "noframes_angrybeaver.cscart_products_categories.product_id"
      ],
      "rows": 1,
      "filtered": 100
    },
    "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 position
9683 273M
9724 1671M
9727 1671M
9728 1671M
9729 1671M
9730 1671M
9731 1671M
9732 1671M
9733 1671M
9734 1671M
9735 1671M
9797 1614M
9798 1614M
9799 1614M
9800 1614M
9801 1614M
9802 1614M
9803 1614M
9804 1614M
9805 1614M
9806 1614M
9807 1614M
9808 1614M
9809 1614M
9810 1614M
9811 1614M
9812 1614M
9905 1666M
9945 1671M
10041 1666M
10094 1666M
10097 1666M
10168 1666M
10181 1671M
10301 1644M
10302 1644M
10303 1644M
10304 1644M
10361 1646M
10362 1646M
10363 1646M
10364 1646M
10365 1646M
10366 1646M
10367 1646M
10368 1646M
10370 1652M
10371 1652M
10372 1652M
10373 1652M
10374 1652M
10375 1652M
10376 1652M
10377 1652M
10378 1652M
10379 1652M
10380 1652M
10381 1652M
10386 1650M
10387 1650M
10388 1650M
10389 1650M
10390 1650M
10392 1650M
10393 1650M
10394 1650M
10395 1650M
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
10447 1656M
10448 1656M
10449 1656M
10450 1656M
10451 1656M
10452 1656M
10453 1656M
10454 1656M
10455 1656M
10456 1656M
10522 1651M
10601 1671M
10606 1666M
10637 1650M
10638 1650M
10639 1650M
10640 1650M
10641 1650M
10643 1666M
10704 1662M
10705 1662M
10706 1662M
10707 1662M
10708 1662M
10709 1662M
10710 1662M
10711 1662M
10712 1662M
10744 1668M
10745 1668M
10746 1668M
12691 1656M
12692 1656M
12693 1656M
12694 1656M
12695 1656M
12696 1656M
12697 1656M
12698 1656M
12699 1656M
12703 1666M