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 (
    9846, 
    9845, 
    9860, 
    9859, 
    9858, 
    9857, 
    9852, 
    9861, 
    9862, 
    9847, 
    9863, 
    9856, 
    9855, 
    9838, 
    9849, 
    9850, 
    9853, 
    9842, 
    9851, 
    9854, 
    10263, 
    10264, 
    10265, 
    10266, 
    10267, 
    10268, 
    10269, 
    10270, 
    10271, 
    10272, 
    10273, 
    15943, 
    15951, 
    15952, 
    15956, 
    15958, 
    15959, 
    15944, 
    15960, 
    15961, 
    15962, 
    15966, 
    15957, 
    15965, 
    15945, 
    15938, 
    15968, 
    15946, 
    15947, 
    15949, 
    15950, 
    12520, 
    12497, 
    12528, 
    12512, 
    12511, 
    12527, 
    10580, 
    10579, 
    9936, 
    9931, 
    12521, 
    12504, 
    12502, 
    12505, 
    13099, 
    12514, 
    12513, 
    9759, 
    10496, 
    10560, 
    10561, 
    10581, 
    10102, 
    9940, 
    10578, 
    10143, 
    9947, 
    10567, 
    10568, 
    10565, 
    9933, 
    12499, 
    12500, 
    10200, 
    10201, 
    10566, 
    10545, 
    10227, 
    10552, 
    12506, 
    10693, 
    12508, 
    9953, 
    12522, 
    12507, 
    12498, 
    13101, 
    13100, 
    10699, 
    10695, 
    10195, 
    10576, 
    10696, 
    9760, 
    10583, 
    10701, 
    10582, 
    10139, 
    12503, 
    10575, 
    9932, 
    10075, 
    10135, 
    9952, 
    10074, 
    10573, 
    10577, 
    10651, 
    10642, 
    12529, 
    12515, 
    12525, 
    12532, 
    12523, 
    10198, 
    10557, 
    10202
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00917

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": 192,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (9846,9845,9860,9859,9858,9857,9852,9861,9862,9847,9863,9856,9855,9838,9849,9850,9853,9842,9851,9854,10263,10264,10265,10266,10267,10268,10269,10270,10271,10272,10273,15943,15951,15952,15956,15958,15959,15944,15960,15961,15962,15966,15957,15965,15945,15938,15968,15946,15947,15949,15950,12520,12497,12528,12512,12511,12527,10580,10579,9936,9931,12521,12504,12502,12505,13099,12514,12513,9759,10496,10560,10561,10581,10102,9940,10578,10143,9947,10567,10568,10565,9933,12499,12500,10200,10201,10566,10545,10227,10552,12506,10693,12508,9953,12522,12507,12498,13101,13100,10699,10695,10195,10576,10696,9760,10583,10701,10582,10139,12503,10575,9932,10075,10135,9952,10074,10573,10577,10651,10642,12529,12515,12525,12532,12523,10198,10557,10202)"
    },
    "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
9759 275M
9760 275M
9838 323M
9842 323M
9845 323M
9846 323M
9847 323M
9849 323M
9850 323M
9851 323M
9852 323M
9853 323M
9854 323M
9855 323M
9856 323M
9857 323M
9858 323M
9859 323M
9860 323M
9861 323M
9862 323M
9863 323M
9931 275M
9932 275M
9933 275M
9936 275M
9940 275M
9947 275M
9952 275M
9953 275M
10074 275M
10075 275M
10102 275M
10135 275M
10139 275M
10143 275M
10195 275M
10198 275M
10200 275M
10201 275M
10202 275M
10227 275M
10263 323M
10264 323M
10265 323M
10266 323M
10267 323M
10268 323M
10269 323M
10270 323M
10271 323M
10272 323M
10273 323M
10496 275M
10545 275M
10552 275M
10557 275M
10560 275M
10561 275M
10565 275M
10566 275M
10567 275M
10568 275M
10573 275M
10575 275M
10576 275M
10577 275M
10578 275M
10579 275M
10580 275M
10581 275M
10582 275M
10583 275M
10642 275M
10651 275M
10693 275M
10695 275M
10696 275M
10699 275M
10701 275M
12497 275M
12498 275M
12499 275M
12500 275M
12502 275M
12503 275M
12504 275M
12505 275M
12506 275M
12507 275M
12508 275M
12511 275M
12512 275M
12513 275M
12514 275M
12515 275M
12520 275M
12521 275M
12522 275M
12523 275M
12525 275M
12527 275M
12528 275M
12529 275M
12532 275M
13099 275M
13100 275M
13101 275M
15938 277,267M
15943 272M
15944 272M
15945 272M
15946 272M
15947 272M
15949 272M
15950 272M
15951 272M
15952 272M
15956 272M
15957 272M
15958 272M
15959 272M
15960 272M
15961 272M
15962 272M
15965 272M
15966 272M
15968 272M