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 (
    9824, 
    9825, 
    9826, 
    9827, 
    9828, 
    9829, 
    9830, 
    10241, 
    10242, 
    10243, 
    10244, 
    10245, 
    10246, 
    10247, 
    10248, 
    10249, 
    10250, 
    10251, 
    10252, 
    10253, 
    10254, 
    10255, 
    10256, 
    10257, 
    10258, 
    10259, 
    10260, 
    10261, 
    10713, 
    10714, 
    10715, 
    10716, 
    10717, 
    10718, 
    10719, 
    10720, 
    10721, 
    10722, 
    10723, 
    10739, 
    10740, 
    10741, 
    10742, 
    10305, 
    10306, 
    10307, 
    10308, 
    10309, 
    10310, 
    10311, 
    10312, 
    12999, 
    13000, 
    13001, 
    13002, 
    13003, 
    13004, 
    13005, 
    13006, 
    13007, 
    13008, 
    13009, 
    13010, 
    13011, 
    13012, 
    10759, 
    10760, 
    10761, 
    10762, 
    10763, 
    10764, 
    10765, 
    10766, 
    9874, 
    9875, 
    9887, 
    9879, 
    9796, 
    9942, 
    9943, 
    9941, 
    9944, 
    10813, 
    10814, 
    10815, 
    10816, 
    10817, 
    10818, 
    10819, 
    10820, 
    10821, 
    10822, 
    10823, 
    10824, 
    10825, 
    10826, 
    12969, 
    10144, 
    10145, 
    10146, 
    10147, 
    10148, 
    10149, 
    10150, 
    10151, 
    10152, 
    10293, 
    10294, 
    10295, 
    10724, 
    10725, 
    10343, 
    10345, 
    10346, 
    10347, 
    10348, 
    10349, 
    10350, 
    10351, 
    10352, 
    10353, 
    10354, 
    10355, 
    10356, 
    10357, 
    10358, 
    10359, 
    10360
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00163

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 (9824,9825,9826,9827,9828,9829,9830,10241,10242,10243,10244,10245,10246,10247,10248,10249,10250,10251,10252,10253,10254,10255,10256,10257,10258,10259,10260,10261,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10739,10740,10741,10742,10305,10306,10307,10308,10309,10310,10311,10312,12999,13000,13001,13002,13003,13004,13005,13006,13007,13008,13009,13010,13011,13012,10759,10760,10761,10762,10763,10764,10765,10766,9874,9875,9887,9879,9796,9942,9943,9941,9944,10813,10814,10815,10816,10817,10818,10819,10820,10821,10822,10823,10824,10825,10826,12969,10144,10145,10146,10147,10148,10149,10150,10151,10152,10293,10294,10295,10724,10725,10343,10345,10346,10347,10348,10349,10350,10351,10352,10353,10354,10355,10356,10357,10358,10359,10360)"
    },
    "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
9796 1672M
9824 1622M
9825 1622M
9826 1622M
9827 1622M
9828 1622M
9829 1622M
9830 1622M
9874 1672M
9875 1672M
9879 1672M
9887 1672M
9941 1672M
9942 1672M
9943 1672M
9944 1672M
10144 1151M
10145 1151M
10146 1151M
10147 1151M
10148 1151M
10149 1151M
10150 1151M
10151 1151M
10152 1151M
10241 1622M
10242 1622M
10243 1622M
10244 1622M
10245 1622M
10246 1622M
10247 1622M
10248 1622M
10249 1622M
10250 1622M
10251 1622M
10252 1622M
10253 1622M
10254 1622M
10255 1622M
10256 1622M
10257 1622M
10258 1622M
10259 1622M
10260 1622M
10261 1622M
10293 775M
10294 775M
10295 775M
10305 1647M
10306 1647M
10307 1647M
10308 1647M
10309 1647M
10310 1647M
10311 1647M
10312 1647M
10343 1646M
10345 1646M
10346 1646M
10347 1646M
10348 1646M
10349 1646M
10350 1646M
10351 1646M
10352 1646M
10353 1646M
10354 1646M
10355 1646M
10356 1646M
10357 1646M
10358 1646M
10359 1646M
10360 1646M
10713 1663M
10714 1663M
10715 1663M
10716 1663M
10717 1663M
10718 1663M
10719 1663M
10720 1663M
10721 1663M
10722 1663M
10723 1663M
10724 775M
10725 775M
10739 1669M
10740 1669M
10741 1669M
10742 1669M
10759 1678M
10760 1678M
10761 1678M
10762 1678M
10763 1678M
10764 1678M
10765 1678M
10766 1678M
10813 1672M
10814 1672M
10815 1672M
10816 1672M
10817 1672M
10818 1672M
10819 1672M
10820 1672M
10821 1672M
10822 1672M
10823 1672M
10824 1672M
10825 1672M
10826 1672M
12969 301,1151M
12999 1682,301M
13000 1682,301M
13001 1682,301M
13002 1682,301M
13003 1682,301M
13004 1682,301M
13005 1682,301M
13006 1682,301M
13007 1682,301M
13008 1682,301M
13009 1682,301M
13010 1682,301M
13011 1682,301M
13012 1682,301M