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 = 283 
WHERE 
  cscart_products_categories.product_id IN (
    9984, 
    9985, 
    9986, 
    9987, 
    9988, 
    9989, 
    9990, 
    9991, 
    9992, 
    9993, 
    9994, 
    9995, 
    9996, 
    9997, 
    9998, 
    9999, 
    10747, 
    10748, 
    10749, 
    10750, 
    10751, 
    10752, 
    9972, 
    9973, 
    9975, 
    9976, 
    9977, 
    9978, 
    9979, 
    10179, 
    10180, 
    10523, 
    10073, 
    10077, 
    10076, 
    10086, 
    10085, 
    10084, 
    10083, 
    10082, 
    10081, 
    10080, 
    10079, 
    10584, 
    10594, 
    10739, 
    10740, 
    10741, 
    10742, 
    10759, 
    10760, 
    10761, 
    10762, 
    10763, 
    10764, 
    10765, 
    10766, 
    10778, 
    10779, 
    10780, 
    10781, 
    10782, 
    10783, 
    10784, 
    10785, 
    10786, 
    10787, 
    10788, 
    10789, 
    10790, 
    10791, 
    10767, 
    10768, 
    10769, 
    10770, 
    10771, 
    10772, 
    10773, 
    10774, 
    10775, 
    10776, 
    10777, 
    12944, 
    12985, 
    12986, 
    12987, 
    12988, 
    12989, 
    12990, 
    12991, 
    12992, 
    12993, 
    12994, 
    10063, 
    10052, 
    10072, 
    10046, 
    10059, 
    10064, 
    10057, 
    10048, 
    10062, 
    10070, 
    10058, 
    10045, 
    10054, 
    10047, 
    10071, 
    10067, 
    10051, 
    10060, 
    10055, 
    10043, 
    10068, 
    10065, 
    10061, 
    10050, 
    10056, 
    10044, 
    10069, 
    10066, 
    10024, 
    10025, 
    10026, 
    10027, 
    10028, 
    10029, 
    10030
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00196

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 (9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,10747,10748,10749,10750,10751,10752,9972,9973,9975,9976,9977,9978,9979,10179,10180,10523,10073,10077,10076,10086,10085,10084,10083,10082,10081,10080,10079,10584,10594,10739,10740,10741,10742,10759,10760,10761,10762,10763,10764,10765,10766,10778,10779,10780,10781,10782,10783,10784,10785,10786,10787,10788,10789,10790,10791,10767,10768,10769,10770,10771,10772,10773,10774,10775,10776,10777,12944,12985,12986,12987,12988,12989,12990,12991,12992,12993,12994,10063,10052,10072,10046,10059,10064,10057,10048,10062,10070,10058,10045,10054,10047,10071,10067,10051,10060,10055,10043,10068,10065,10061,10050,10056,10044,10069,10066,10024,10025,10026,10027,10028,10029,10030)"
    },
    "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
9972 1641M
9973 1641M
9975 1641M
9976 1641M
9977 1641M
9978 1641M
9979 1641M
9984 1638M
9985 1638M
9986 1638M
9987 1638M
9988 1638M
9989 1638M
9990 1638M
9991 1638M
9992 1638M
9993 1638M
9994 1638M
9995 1638M
9996 1638M
9997 1638M
9998 1638M
9999 1638M
10024 1636M
10025 1636M
10026 1636M
10027 1636M
10028 1636M
10029 1636M
10030 1636M
10043 1629M
10044 1629M
10045 1629M
10046 1629M
10047 1629M
10048 1629M
10050 1629M
10051 1629M
10052 1629M
10054 1629M
10055 1629M
10056 1629M
10057 1629M
10058 1629M
10059 1629M
10060 1629M
10061 1629M
10062 1629M
10063 1629M
10064 1629M
10065 1629M
10066 1629M
10067 1629M
10068 1629M
10069 1629M
10070 1629M
10071 1629M
10072 1629M
10073 1674M
10076 1674M
10077 1674M
10079 1674M
10080 1674M
10081 1674M
10082 1674M
10083 1674M
10084 1674M
10085 1674M
10086 1674M
10179 1674M
10180 1674M
10523 1674M
10584 1674M
10594 1674M
10739 1669M
10740 1669M
10741 1669M
10742 1669M
10747 1677M
10748 1677M
10749 1677M
10750 1677M
10751 1677M
10752 1677M
10759 1678M
10760 1678M
10761 1678M
10762 1678M
10763 1678M
10764 1678M
10765 1678M
10766 1678M
10767 1681M
10768 1681M
10769 1681M
10770 1681M
10771 1681M
10772 1681M
10773 1681M
10774 1681M
10775 1681M
10776 1681M
10777 1681M
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
12944 283M 0
12985 1649M
12986 1649M
12987 1649M
12988 1649M
12989 1649M
12990 1649M
12991 1649M
12992 1649M
12993 1649M
12994 1649M