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 (
    10177, 
    10175, 
    9813, 
    10095, 
    9904, 
    10178, 
    10644, 
    10093, 
    10096, 
    10167, 
    10525, 
    10526, 
    10527, 
    10528, 
    10529, 
    10530, 
    10531, 
    10532, 
    10533, 
    10534, 
    10535, 
    10536, 
    10537, 
    10538, 
    10539, 
    10540, 
    10541, 
    10542, 
    10524, 
    10543, 
    10544, 
    13013, 
    13014, 
    13015, 
    13016, 
    13017, 
    13018, 
    13019, 
    13020, 
    13021, 
    13022, 
    13023, 
    13024, 
    13025, 
    13026, 
    13027, 
    13028, 
    13029, 
    13030, 
    10688, 
    10727, 
    10728, 
    10729, 
    10730, 
    10731, 
    10732, 
    10733, 
    10313, 
    10314, 
    10315, 
    10316, 
    10317, 
    10318, 
    10319, 
    10320, 
    10321, 
    10322, 
    9690, 
    10106, 
    10105, 
    10104, 
    10103, 
    10206, 
    10209, 
    10216, 
    10203, 
    10204, 
    10205, 
    10207, 
    10208, 
    10210, 
    10211, 
    10212, 
    10213, 
    10214, 
    10215, 
    10217, 
    10218, 
    10219, 
    10220, 
    10221, 
    10222, 
    10323, 
    10324, 
    10325, 
    10326, 
    10327, 
    10328, 
    10329, 
    10330, 
    10331, 
    10332, 
    10333, 
    10334, 
    10335, 
    10336, 
    10337, 
    10338, 
    10339, 
    10340, 
    10341, 
    10342, 
    10778, 
    10779, 
    10780, 
    10781, 
    10782, 
    10783, 
    10784, 
    10785, 
    10786, 
    10787, 
    10788, 
    10789, 
    10790, 
    10791, 
    9688, 
    10792
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00393

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 (10177,10175,9813,10095,9904,10178,10644,10093,10096,10167,10525,10526,10527,10528,10529,10530,10531,10532,10533,10534,10535,10536,10537,10538,10539,10540,10541,10542,10524,10543,10544,13013,13014,13015,13016,13017,13018,13019,13020,13021,13022,13023,13024,13025,13026,13027,13028,13029,13030,10688,10727,10728,10729,10730,10731,10732,10733,10313,10314,10315,10316,10317,10318,10319,10320,10321,10322,9690,10106,10105,10104,10103,10206,10209,10216,10203,10204,10205,10207,10208,10210,10211,10212,10213,10214,10215,10217,10218,10219,10220,10221,10222,10323,10324,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10778,10779,10780,10781,10782,10783,10784,10785,10786,10787,10788,10789,10790,10791,9688,10792)"
    },
    "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
9688 273M
9690 273M
9813 1666M
9904 1666M
10093 1666M
10095 1666M
10096 1666M
10103 1618M
10104 1618M
10105 1618M
10106 1618M
10167 1666M
10175 1666M
10177 1666M
10178 1666M
10203 1637M
10204 1637M
10205 1637M
10206 1637M
10207 1637M
10208 1637M
10209 1637M
10210 1637M
10211 1637M
10212 1637M
10213 1637M
10214 1637M
10215 1637M
10216 1637M
10217 1637M
10218 1637M
10219 1637M
10220 1637M
10221 1637M
10222 1637M
10313 1643M
10314 1643M
10315 1643M
10316 1643M
10317 1643M
10318 1643M
10319 1643M
10320 1643M
10321 1643M
10322 1643M
10323 1648M
10324 1648M
10325 1648M
10326 1648M
10327 1648M
10328 1648M
10329 1648M
10330 1648M
10331 1648M
10332 1648M
10333 1648M
10334 1648M
10335 1648M
10336 1648M
10337 1648M
10338 1648M
10339 1648M
10340 1648M
10341 1648M
10342 1648M
10524 1657M
10525 1657M
10526 1657M
10527 1657M
10528 1657M
10529 1657M
10530 1657M
10531 1657M
10532 1657M
10533 1657M
10534 1657M
10535 1657M
10536 1657M
10537 1657M
10538 1657M
10539 1657M
10540 1657M
10541 1657M
10542 1657M
10543 1657M
10544 1657M
10644 1666M
10688 1664M
10727 1664M
10728 1664M
10729 1664M
10730 1664M
10731 1664M
10732 1664M
10733 1664M
10778 1675M
10779 1675M
10780 1675M
10781 1675M
10782 1675M
10783 1675M
10784 1675M
10785 1675M
10786 1675M
10787 1675M
10788 1675M
10789 1675M
10790 1675M
10791 1675M
10792 1676M
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
13029 301,1683M
13030 301,1683M