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 (
    10734, 
    10735, 
    10736, 
    10737, 
    10738, 
    10668, 
    10669, 
    10670, 
    10671, 
    10672, 
    10673, 
    10674, 
    10675, 
    10676, 
    10677, 
    10678, 
    10679, 
    10680, 
    10681, 
    10682, 
    10683, 
    10684, 
    10685, 
    10686, 
    10607, 
    10608, 
    10609, 
    10610, 
    10611, 
    10612, 
    10613, 
    10614, 
    10615, 
    10616, 
    10617, 
    10618, 
    10619, 
    10620, 
    10621, 
    10622, 
    10623, 
    10624, 
    9814, 
    9815, 
    9816, 
    9817, 
    9818, 
    15732, 
    9820, 
    9821, 
    9822, 
    9823, 
    9824, 
    9825, 
    9826, 
    9827, 
    9828, 
    9829, 
    9830, 
    10241, 
    10242, 
    10243, 
    10244, 
    10245, 
    10246, 
    10247, 
    10248, 
    10249, 
    10250, 
    10251, 
    10252, 
    10253, 
    10254, 
    10255, 
    10256, 
    10257, 
    10258, 
    10259, 
    10260, 
    10261, 
    10425, 
    10426, 
    10427, 
    10428, 
    10429, 
    10430, 
    10431, 
    10432, 
    10433, 
    10434, 
    10435, 
    10436, 
    10437, 
    10438, 
    10439, 
    10440, 
    10441, 
    10442, 
    10443, 
    10444, 
    10522, 
    9803, 
    9805, 
    9797, 
    9800, 
    9799, 
    9798, 
    9801, 
    9802, 
    9806, 
    9804, 
    9809, 
    9810, 
    9811, 
    9812, 
    9807, 
    9808, 
    9905, 
    10168, 
    10041, 
    10643, 
    10606, 
    10094, 
    10097, 
    12703, 
    10177, 
    10175, 
    9813
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00142

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 (10734,10735,10736,10737,10738,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10607,10608,10609,10610,10611,10612,10613,10614,10615,10616,10617,10618,10619,10620,10621,10622,10623,10624,9814,9815,9816,9817,9818,15732,9820,9821,9822,9823,9824,9825,9826,9827,9828,9829,9830,10241,10242,10243,10244,10245,10246,10247,10248,10249,10250,10251,10252,10253,10254,10255,10256,10257,10258,10259,10260,10261,10425,10426,10427,10428,10429,10430,10431,10432,10433,10434,10435,10436,10437,10438,10439,10440,10441,10442,10443,10444,10522,9803,9805,9797,9800,9799,9798,9801,9802,9806,9804,9809,9810,9811,9812,9807,9808,9905,10168,10041,10643,10606,10094,10097,12703,10177,10175,9813)"
    },
    "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
9797 1614M
9798 1614M
9799 1614M
9800 1614M
9801 1614M
9802 1614M
9803 1614M
9804 1614M
9805 1614M
9806 1614M
9807 1614M
9808 1614M
9809 1614M
9810 1614M
9811 1614M
9812 1614M
9813 1666M
9814 1622M
9815 1622M
9816 1622M
9817 1622M
9818 1622M
9820 1622M
9821 1622M
9822 1622M
9823 1622M
9824 1622M
9825 1622M
9826 1622M
9827 1622M
9828 1622M
9829 1622M
9830 1622M
9905 1666M
10041 1666M
10094 1666M
10097 1666M
10168 1666M
10175 1666M
10177 1666M
10241 1622M
10242 1622M
10243 1622M
10244 1622M
10245 1622M
10246 1622M
10247 1622M
10248 1622M
10249 1622M
10250 1622M
10251 1622M
10252 1622M
10253 1622M
10254 1622M
10255 1622M
10256 1622M
10257 1622M
10258 1622M
10259 1622M
10260 1622M
10261 1622M
10425 1651M
10426 1651M
10427 1651M
10428 1651M
10429 1651M
10430 1651M
10431 1651M
10432 1651M
10433 1651M
10434 1651M
10435 1651M
10436 1651M
10437 1651M
10438 1651M
10439 1651M
10440 1651M
10441 1651M
10442 1651M
10443 1651M
10444 1651M
10522 1651M
10606 1666M
10607 1659M
10608 1659M
10609 1659M
10610 1659M
10611 1659M
10612 1659M
10613 1659M
10614 1659M
10615 1659M
10616 1659M
10617 1659M
10618 1659M
10619 1659M
10620 1659M
10621 1659M
10622 1659M
10623 1659M
10624 1659M
10643 1666M
10668 1661M
10669 1661M
10670 1661M
10671 1661M
10672 1661M
10673 1661M
10674 1661M
10675 1661M
10676 1661M
10677 1661M
10678 1661M
10679 1661M
10680 1661M
10681 1661M
10682 1661M
10683 1661M
10684 1661M
10685 1661M
10686 1661M
10734 1665M
10735 1665M
10736 1665M
10737 1665M
10738 1665M
12703 1666M
15732 1622M