Sandbox

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