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 (
    9984, 
    9985, 
    9986, 
    9987, 
    9988, 
    9989, 
    9990, 
    9991, 
    9992, 
    9993, 
    9994, 
    9995, 
    9996, 
    9997, 
    9998, 
    9999, 
    12995, 
    12996, 
    12997, 
    12998, 
    10734, 
    10735, 
    10736, 
    10737, 
    10738, 
    10747, 
    10748, 
    10749, 
    10750, 
    10751, 
    10752, 
    15744, 
    15745, 
    15746, 
    15743, 
    15738, 
    15739, 
    15740, 
    15741, 
    15742, 
    15736, 
    15737, 
    10668, 
    10669, 
    10670, 
    10671, 
    10672, 
    10673, 
    10674, 
    10675, 
    10676, 
    10677, 
    10678, 
    10679, 
    10680, 
    10681, 
    10682, 
    10683, 
    10684, 
    10685, 
    10686, 
    10607, 
    10608, 
    10609, 
    10610, 
    10611, 
    10612, 
    10613, 
    10614, 
    10615, 
    10616, 
    10617, 
    10618, 
    10619, 
    10620, 
    10621, 
    10622, 
    10623, 
    10624, 
    9685, 
    10653, 
    10646, 
    10645, 
    10647, 
    10648, 
    10649, 
    10726, 
    10650, 
    10547, 
    9789, 
    10655, 
    10654, 
    10605, 
    10604, 
    9873, 
    9972, 
    9973, 
    9975, 
    9976, 
    9977, 
    9978, 
    9979, 
    10179, 
    10180, 
    10523, 
    10073, 
    10077, 
    10076, 
    10086, 
    10085, 
    10084, 
    10083, 
    10082, 
    10081, 
    10080, 
    10079, 
    10584, 
    10594, 
    9814, 
    9815, 
    9816, 
    9817, 
    9818, 
    15732, 
    9820, 
    9821, 
    9822, 
    9823
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00206

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": 208,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,12995,12996,12997,12998,10734,10735,10736,10737,10738,10747,10748,10749,10750,10751,10752,15744,15745,15746,15743,15738,15739,15740,15741,15742,15736,15737,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10607,10608,10609,10610,10611,10612,10613,10614,10615,10616,10617,10618,10619,10620,10621,10622,10623,10624,9685,10653,10646,10645,10647,10648,10649,10726,10650,10547,9789,10655,10654,10605,10604,9873,9972,9973,9975,9976,9977,9978,9979,10179,10180,10523,10073,10077,10076,10086,10085,10084,10083,10082,10081,10080,10079,10584,10594,9814,9815,9816,9817,9818,15732,9820,9821,9822,9823)"
    },
    "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
9685 273M
9789 700M
9814 1622M
9815 1622M
9816 1622M
9817 1622M
9818 1622M
9820 1622M
9821 1622M
9822 1622M
9823 1622M
9873 708M
9972 1641M
9973 1641M
9975 1641M
9976 1641M
9977 1641M
9978 1641M
9979 1641M
9984 1638M
9985 1638M
9986 1638M
9987 1638M
9988 1638M
9989 1638M
9990 1638M
9991 1638M
9992 1638M
9993 1638M
9994 1638M
9995 1638M
9996 1638M
9997 1638M
9998 1638M
9999 1638M
10073 1674M
10076 1674M
10077 1674M
10079 1674M
10080 1674M
10081 1674M
10082 1674M
10083 1674M
10084 1674M
10085 1674M
10086 1674M
10179 1674M
10180 1674M
10523 1674M
10547 700M
10584 1674M
10594 1674M
10604 708M
10605 708M
10607 1659M
10608 1659M
10609 1659M
10610 1659M
10611 1659M
10612 1659M
10613 1659M
10614 1659M
10615 1659M
10616 1659M
10617 1659M
10618 1659M
10619 1659M
10620 1659M
10621 1659M
10622 1659M
10623 1659M
10624 1659M
10645 700M
10646 700M
10647 700M
10648 700M
10649 700M
10650 700M
10653 700M
10654 708M
10655 708M
10668 1661M
10669 1661M
10670 1661M
10671 1661M
10672 1661M
10673 1661M
10674 1661M
10675 1661M
10676 1661M
10677 1661M
10678 1661M
10679 1661M
10680 1661M
10681 1661M
10682 1661M
10683 1661M
10684 1661M
10685 1661M
10686 1661M
10726 700M
10734 1665M
10735 1665M
10736 1665M
10737 1665M
10738 1665M
10747 1677M
10748 1677M
10749 1677M
10750 1677M
10751 1677M
10752 1677M
12995 1684,301M
12996 1684,301M
12997 1684,301M
12998 1684,301M
15732 1622M
15736 301,1673M
15737 301,1673M
15738 301,1673M
15739 1673,301M
15740 301,1673M
15741 301,1673M
15742 1673,301M
15743 301,1673M
15744 301,1673M
15745 301,1673M
15746 301,1673M