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 = 286 
WHERE 
  cscart_products_categories.product_id IN (
    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, 
    10323, 
    10324, 
    10325, 
    10326, 
    10327, 
    10328, 
    10329, 
    10330, 
    10331, 
    10332, 
    10333, 
    10334, 
    10335, 
    10336, 
    10337, 
    10338, 
    10339, 
    10340, 
    10341, 
    10342, 
    10792, 
    10793, 
    10794, 
    10795, 
    10796, 
    10797, 
    10798, 
    10799, 
    10800, 
    10801, 
    10802, 
    10803, 
    10804, 
    10805, 
    10806, 
    10807, 
    10808, 
    10809, 
    10810, 
    10811, 
    10812, 
    10827, 
    10828, 
    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, 
    9881, 
    9896, 
    9892, 
    10481
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00224

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 (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,10323,10324,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10792,10793,10794,10795,10796,10797,10798,10799,10800,10801,10802,10803,10804,10805,10806,10807,10808,10809,10810,10811,10812,10827,10828,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,9881,9896,9892,10481)"
    },
    "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
9881 327M
9883 327M
9892 327M
9896 327M
9899 327M
9901 327M
9903 327M
9904 1666M
10093 1666M
10095 1666M
10096 1666M
10167 1666M
10178 1666M
10263 323M
10264 323M
10265 323M
10266 323M
10267 323M
10268 323M
10269 323M
10270 323M
10271 323M
10272 323M
10273 323M
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
10468 327M
10473 327M
10476 327M
10478 327M
10479 327M
10481 327M
10485 327M
10486 327M
10487 327M
10492 327M
10493 327M
10494 327M
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
10792 1676M
10793 1676M
10794 1676M
10795 1676M
10796 1676M
10797 1676M
10798 1676M
10799 1676M
10800 1676M
10801 1676M
10802 1676M
10803 1676M
10804 1676M
10805 1676M
10806 1676M
10807 1676M
10808 1676M
10809 1676M
10810 1676M
10811 1676M
10812 1676M
10827 1676M
10828 1676M
12702 323M