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 (
    10793, 
    10794, 
    10795, 
    10796, 
    10797, 
    10798, 
    10799, 
    10800, 
    10801, 
    10802, 
    10803, 
    10804, 
    10805, 
    10806, 
    10807, 
    10808, 
    10809, 
    10810, 
    10811, 
    10812, 
    10827, 
    10828, 
    9686, 
    10767, 
    10768, 
    10769, 
    10770, 
    10771, 
    10772, 
    10773, 
    10774, 
    10775, 
    10776, 
    10777, 
    9689, 
    10459, 
    10460, 
    10461, 
    10462, 
    10463, 
    10464, 
    10465, 
    10625, 
    10626, 
    10627, 
    10628, 
    10629, 
    10630, 
    10631, 
    10632, 
    10633, 
    10634, 
    10635, 
    10636, 
    12944, 
    10829, 
    10830, 
    10831, 
    10832, 
    10833, 
    10834, 
    10835, 
    10836, 
    10837, 
    10838, 
    10839, 
    12985, 
    12986, 
    12987, 
    12988, 
    12989, 
    12990, 
    12991, 
    12992, 
    12993, 
    12994, 
    10403, 
    10404, 
    10405, 
    15781, 
    10406, 
    10407, 
    10408, 
    10409, 
    10410, 
    10411, 
    10412, 
    12853, 
    12854, 
    12855, 
    10414, 
    10415, 
    10416, 
    12856, 
    10417, 
    10418, 
    10419, 
    10420, 
    10421, 
    10422, 
    10423, 
    10424, 
    10384, 
    10383, 
    10382, 
    10753, 
    10754, 
    10755, 
    10296, 
    10297, 
    10298, 
    10299, 
    10300, 
    10446, 
    10063, 
    10052, 
    10072, 
    10046, 
    10059, 
    10064, 
    10057, 
    10048, 
    10062, 
    10070, 
    10058, 
    10045, 
    10054, 
    10047
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00262

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": 131,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10793,10794,10795,10796,10797,10798,10799,10800,10801,10802,10803,10804,10805,10806,10807,10808,10809,10810,10811,10812,10827,10828,9686,10767,10768,10769,10770,10771,10772,10773,10774,10775,10776,10777,9689,10459,10460,10461,10462,10463,10464,10465,10625,10626,10627,10628,10629,10630,10631,10632,10633,10634,10635,10636,12944,10829,10830,10831,10832,10833,10834,10835,10836,10837,10838,10839,12985,12986,12987,12988,12989,12990,12991,12992,12993,12994,10403,10404,10405,15781,10406,10407,10408,10409,10410,10411,10412,12853,12854,12855,10414,10415,10416,12856,10417,10418,10419,10420,10421,10422,10423,10424,10384,10383,10382,10753,10754,10755,10296,10297,10298,10299,10300,10446,10063,10052,10072,10046,10059,10064,10057,10048,10062,10070,10058,10045,10054,10047)"
    },
    "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
9686 273M
9689 273M
10045 1629M
10046 1629M
10047 1629M
10048 1629M
10052 1629M
10054 1629M
10057 1629M
10058 1629M
10059 1629M
10062 1629M
10063 1629M
10064 1629M
10070 1629M
10072 1629M
10296 1645M
10297 1645M
10298 1645M
10299 1645M
10300 1645M
10382 1654M
10383 1654M
10384 1654M
10403 1655M
10404 1655M
10405 1655M
10406 1655M
10407 1655M
10408 1655M
10409 1655M
10410 1655M
10411 1655M
10412 1655M
10414 1655M
10415 1655M
10416 1655M
10417 1655M
10418 1655M
10419 1655M
10420 1655M
10421 1655M
10422 1655M
10423 1655M
10424 1655M
10446 1645M
10459 1658M
10460 1658M
10461 1658M
10462 1658M
10463 1658M
10464 1658M
10465 1658M
10625 1660M
10626 1660M
10627 1660M
10628 1660M
10629 1660M
10630 1660M
10631 1660M
10632 1660M
10633 1660M
10634 1660M
10635 1660M
10636 1660M
10753 1680M
10754 1680M
10755 1680M
10767 1681M
10768 1681M
10769 1681M
10770 1681M
10771 1681M
10772 1681M
10773 1681M
10774 1681M
10775 1681M
10776 1681M
10777 1681M
10793 1676M
10794 1676M
10795 1676M
10796 1676M
10797 1676M
10798 1676M
10799 1676M
10800 1676M
10801 1676M
10802 1676M
10803 1676M
10804 1676M
10805 1676M
10806 1676M
10807 1676M
10808 1676M
10809 1676M
10810 1676M
10811 1676M
10812 1676M
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
12853 1655M
12854 1655M
12855 1655M
12856 1655M
12944 283M
12985 1649M
12986 1649M
12987 1649M
12988 1649M
12989 1649M
12990 1649M
12991 1649M
12992 1649M
12993 1649M
12994 1649M
15781 1655M