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 = 280 
WHERE 
  cscart_products_categories.product_id IN (
    10068, 
    10065, 
    10061, 
    10050, 
    10056, 
    10044, 
    10069, 
    10066, 
    12671, 
    12672, 
    15779, 
    12673, 
    12674, 
    12675, 
    12676, 
    12677, 
    12678, 
    12679, 
    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, 
    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, 
    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, 
    10478, 
    9883, 
    9901, 
    10493, 
    10485, 
    10476, 
    10479, 
    10494, 
    10492, 
    10468, 
    9903, 
    10487, 
    9899, 
    10473, 
    10486
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00148

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": 131,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10068,10065,10061,10050,10056,10044,10069,10066,12671,12672,15779,12673,12674,12675,12676,12677,12678,12679,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,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,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,10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486)"
    },
    "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
9838 323M
9839 323M
9840 323M
9841 323M
9842 323M
9843 323M
9844 323M
9845 323M
9846 323M
9847 323M
9848 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
9883 327M
9899 327M
9901 327M
9903 327M
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
10044 1629M
10050 1629M
10056 1629M
10061 1629M
10065 1629M
10066 1629M
10068 1629M
10069 1629M
10263 323M
10264 323M
10265 323M
10266 323M
10267 323M
10268 323M
10269 323M
10270 323M
10271 323M
10272 323M
10273 323M
10397 787M
10398 787M
10399 787M
10400 787M
10401 787M
10402 787M
10468 327M
10473 327M
10476 327M
10478 327M
10479 327M
10485 327M
10486 327M
10487 327M
10492 327M
10493 327M
10494 327M
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
12671 784M
12672 784M
12673 784M
12674 784M
12675 784M
12676 784M
12677 784M
12678 784M
12679 784M
12700 787M
12702 323M
15779 784M