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 (
    10071, 
    10067, 
    10051, 
    10060, 
    10055, 
    10043, 
    10068, 
    10065, 
    10061, 
    10050, 
    10056, 
    10044, 
    10069, 
    10066, 
    12671, 
    12672, 
    15779, 
    12673, 
    12674, 
    12675, 
    12676, 
    12677, 
    12678, 
    12679, 
    9687, 
    10024, 
    10025, 
    10026, 
    10027, 
    10028, 
    10029, 
    10030, 
    10031, 
    10032, 
    10033, 
    10034, 
    10035, 
    10036, 
    10037, 
    10038, 
    10039, 
    10040, 
    10756, 
    10757, 
    10758, 
    10397, 
    10398, 
    10399, 
    10400, 
    10401, 
    10402, 
    12700, 
    12546, 
    12633, 
    12573, 
    12548, 
    12580, 
    12576, 
    12565, 
    12567, 
    12549, 
    12581, 
    12557, 
    12558, 
    12570, 
    12578, 
    12568, 
    12547, 
    12569, 
    12584, 
    12592, 
    12575, 
    12574, 
    12550, 
    12544, 
    12564, 
    12577, 
    12579, 
    12555, 
    12560, 
    12556, 
    12561, 
    12572, 
    12583, 
    12591, 
    12582, 
    12590, 
    12571, 
    12632, 
    12559, 
    12631, 
    10596, 
    10597, 
    10598, 
    10599, 
    10600, 
    10521, 
    10520, 
    10519, 
    10518, 
    10517, 
    10516, 
    10515, 
    10514, 
    10513, 
    10512, 
    10511, 
    10510, 
    10509, 
    10508, 
    10507, 
    10506, 
    10505, 
    10504, 
    10503, 
    10502, 
    10501, 
    10500, 
    10499, 
    10498, 
    10497, 
    9839, 
    12702, 
    9841, 
    9848, 
    9840, 
    9844, 
    9843
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00158

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": 170,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10071,10067,10051,10060,10055,10043,10068,10065,10061,10050,10056,10044,10069,10066,12671,12672,15779,12673,12674,12675,12676,12677,12678,12679,9687,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10756,10757,10758,10397,10398,10399,10400,10401,10402,12700,12546,12633,12573,12548,12580,12576,12565,12567,12549,12581,12557,12558,12570,12578,12568,12547,12569,12584,12592,12575,12574,12550,12544,12564,12577,12579,12555,12560,12556,12561,12572,12583,12591,12582,12590,12571,12632,12559,12631,10596,10597,10598,10599,10600,10521,10520,10519,10518,10517,10516,10515,10514,10513,10512,10511,10510,10509,10508,10507,10506,10505,10504,10503,10502,10501,10500,10499,10498,10497,9839,12702,9841,9848,9840,9844,9843)"
    },
    "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
9687	273M
9839	323M
9840	323M
9841	323M
9843	323M
9844	323M
9848	323M
10024	1636M
10025	1636M
10026	1636M
10027	1636M
10028	1636M
10029	1636M
10030	1636M
10031	1636M
10032	1636M
10033	1636M
10034	1636M
10035	1636M
10036	1636M
10037	1636M
10038	1636M
10039	1636M
10040	1636M
10043	1629M
10044	1629M
10050	1629M
10051	1629M
10055	1629M
10056	1629M
10060	1629M
10061	1629M
10065	1629M
10066	1629M
10067	1629M
10068	1629M
10069	1629M
10071	1629M
10397	787M
10398	787M
10399	787M
10400	787M
10401	787M
10402	787M
10497	455M
10498	455M
10499	455M
10500	455M
10501	455M
10502	455M
10503	455M
10504	455M
10505	455M
10506	455M
10507	455M
10508	455M
10509	455M
10510	455M
10511	455M
10512	455M
10513	455M
10514	455M
10515	455M
10516	455M
10517	455M
10518	455M
10519	455M
10520	455M
10521	455M
10596	455M
10597	455M
10598	455M
10599	455M
10600	455M
10756	1679M
10757	1679M
10758	1679M
12544	278,277,267M
12546	277,267,278M
12547	278,267,277M
12548	277,278,267M
12549	267,278,277M
12550	278,267,277M
12555	277,267,278M
12556	277,278,267M
12557	278,267,277M
12558	278,267,277M
12559	278,268,267M
12560	267,277,278M
12561	267,278,277M
12564	277,278,267M
12565	278,277,267M
12567	278,267,277M
12568	267,278,277M
12569	278,277,267M
12570	277,267,278M
12571	267,278,277M
12572	267,277,278M
12573	267,278,277M
12574	277,267,278M
12575	267,278,277M
12576	278,267,277M
12577	267,278,277M
12578	278,268,267M
12579	277,278,267M
12580	278,277,267M
12581	277,267,278M
12582	267,277,278M
12583	267,277,278M
12584	278,267,277M
12590	267,278,277M
12591	277,267,278M
12592	267,277,278M
12631	278,277,267M
12632	277,278,267M
12633	277,278,267M
12671	784M
12672	784M
12673	784M
12674	784M
12675	784M
12676	784M
12677	784M
12678	784M
12679	784M
12700	787M
12702	323M
15779	784M