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 = 291 
WHERE 
  cscart_products_categories.product_id IN (
    12995, 
    12996, 
    12997, 
    12998, 
    10713, 
    10714, 
    10715, 
    10716, 
    10717, 
    10718, 
    10719, 
    10720, 
    10721, 
    10722, 
    10723, 
    10305, 
    10306, 
    10307, 
    10308, 
    10309, 
    10310, 
    10311, 
    10312, 
    12999, 
    13000, 
    13001, 
    13002, 
    13003, 
    13004, 
    13005, 
    13006, 
    13007, 
    13008, 
    13009, 
    13010, 
    13011, 
    13012, 
    9874, 
    9875, 
    9887, 
    9879, 
    9796, 
    9942, 
    9943, 
    9941, 
    9944, 
    10813, 
    10814, 
    10815, 
    10816, 
    10817, 
    10818, 
    10819, 
    10820, 
    10821, 
    10822, 
    10823, 
    10824, 
    10825, 
    10826, 
    10704, 
    10705, 
    10706, 
    10707, 
    10708, 
    10709, 
    10710, 
    10711, 
    10712, 
    10301, 
    10302, 
    10303, 
    10304, 
    9945, 
    10601, 
    9727, 
    9728, 
    9729, 
    9730, 
    9731, 
    9732, 
    9733, 
    10181, 
    9734, 
    9735, 
    9724, 
    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, 
    13013, 
    13014, 
    13015, 
    13016, 
    13017, 
    13018, 
    13019, 
    13020, 
    13021, 
    13022, 
    13023, 
    13024, 
    13025, 
    13026, 
    13027, 
    13028
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00297

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 (12995,12996,12997,12998,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10305,10306,10307,10308,10309,10310,10311,10312,12999,13000,13001,13002,13003,13004,13005,13006,13007,13008,13009,13010,13011,13012,9874,9875,9887,9879,9796,9942,9943,9941,9944,10813,10814,10815,10816,10817,10818,10819,10820,10821,10822,10823,10824,10825,10826,10704,10705,10706,10707,10708,10709,10710,10711,10712,10301,10302,10303,10304,9945,10601,9727,9728,9729,9730,9731,9732,9733,10181,9734,9735,9724,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,13013,13014,13015,13016,13017,13018,13019,13020,13021,13022,13023,13024,13025,13026,13027,13028)"
    },
    "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
9724 1671M
9727 1671M
9728 1671M
9729 1671M
9730 1671M
9731 1671M
9732 1671M
9733 1671M
9734 1671M
9735 1671M
9796 1672M
9874 1672M
9875 1672M
9879 1672M
9887 1672M
9941 1672M
9942 1672M
9943 1672M
9944 1672M
9945 1671M
10181 1671M
10301 1644M
10302 1644M
10303 1644M
10304 1644M
10305 1647M
10306 1647M
10307 1647M
10308 1647M
10309 1647M
10310 1647M
10311 1647M
10312 1647M
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
10601 1671M
10637 1650M
10638 1650M
10639 1650M
10640 1650M
10641 1650M
10704 1662M
10705 1662M
10706 1662M
10707 1662M
10708 1662M
10709 1662M
10710 1662M
10711 1662M
10712 1662M
10713 1663M
10714 1663M
10715 1663M
10716 1663M
10717 1663M
10718 1663M
10719 1663M
10720 1663M
10721 1663M
10722 1663M
10723 1663M
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
12995 1684,301M
12996 1684,301M
12997 1684,301M
12998 1684,301M
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
13013 301,1683M
13014 1683,301M
13015 301,1683M
13016 301,1683M
13017 301,1683M
13018 301,1683M
13019 301,1683M
13020 301,1683M
13021 301,1683M
13022 301,1683M
13023 301,1683M
13024 301,1683M
13025 301,1683M
13026 301,1683M
13027 301,1683M
13028 1683,301M