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 
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') 
WHERE 
  cscart_products_categories.product_id IN (
    12551, 
    12544, 
    12546, 
    12547, 
    12548, 
    12549, 
    12550, 
    12555, 
    12556, 
    12557, 
    12558, 
    12559, 
    12576, 
    12577, 
    12578, 
    9683, 
    9685, 
    9686, 
    9687, 
    9688, 
    9689, 
    9690, 
    12560, 
    12561, 
    9775, 
    9776, 
    9777, 
    9778, 
    9779, 
    9780, 
    9781, 
    9782, 
    9783, 
    9784, 
    9785, 
    9786, 
    9787, 
    9788, 
    9790, 
    9791, 
    9792, 
    9795, 
    9797, 
    9798, 
    9799, 
    9800, 
    9801, 
    9802, 
    9803, 
    9804, 
    9805, 
    9806, 
    9807, 
    9808, 
    9809, 
    9810, 
    9811, 
    9812, 
    9814, 
    9815, 
    9816, 
    9817, 
    9818, 
    9820, 
    9821, 
    9822, 
    9823, 
    9824, 
    9825, 
    9826, 
    9827, 
    9828, 
    9829, 
    9830, 
    15732, 
    9838, 
    9839, 
    9840, 
    9841, 
    9842, 
    9843, 
    9844, 
    9845, 
    9846, 
    9847, 
    9848, 
    9849, 
    9850, 
    9851, 
    9852, 
    9853, 
    9854, 
    9855, 
    9856, 
    9857, 
    9858, 
    9859, 
    9860, 
    9861, 
    9862, 
    9863, 
    9876, 
    9877, 
    9878, 
    9880, 
    9881, 
    9882, 
    9883, 
    9885, 
    9886, 
    9888, 
    9889, 
    9890, 
    9891, 
    9892, 
    9893, 
    9894, 
    9895, 
    9896, 
    9897, 
    9899, 
    9900, 
    9901, 
    9903, 
    9972, 
    9973, 
    9975, 
    9976
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00140

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": 148,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (12551,12544,12546,12547,12548,12549,12550,12555,12556,12557,12558,12559,12576,12577,12578,9683,9685,9686,9687,9688,9689,9690,12560,12561,9775,9776,9777,9778,9779,9780,9781,9782,9783,9784,9785,9786,9787,9788,9790,9791,9792,9795,9797,9798,9799,9800,9801,9802,9803,9804,9805,9806,9807,9808,9809,9810,9811,9812,9814,9815,9816,9817,9818,9820,9821,9822,9823,9824,9825,9826,9827,9828,9829,9830,15732,9838,9839,9840,9841,9842,9843,9844,9845,9846,9847,9848,9849,9850,9851,9852,9853,9854,9855,9856,9857,9858,9859,9860,9861,9862,9863,9876,9877,9878,9880,9881,9882,9883,9885,9886,9888,9889,9890,9891,9892,9893,9894,9895,9896,9897,9899,9900,9901,9903,9972,9973,9975,9976)"
    },
    "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
9683 273M
9685 273M
9686 273M
9687 273M
9688 273M
9689 273M
9690 273M
9775 308M
9776 308M
9777 308M
9778 308M
9779 308M
9780 308M
9781 308M
9782 308M
9783 308M
9784 308M
9785 308M
9786 308M
9787 308M
9788 308M
9790 308M
9791 308M
9792 308M
9795 308M
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
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
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
9876 327M
9877 327M
9878 327M
9880 327M
9881 327M
9882 327M
9883 327M
9885 327M
9886 327M
9888 327M
9889 327M
9890 327M
9891 327M
9892 327M
9893 327M
9894 327M
9895 327M
9896 327M
9897 327M
9899 327M
9900 327M
9901 327M
9903 327M
9972 1641M
9973 1641M
9975 1641M
9976 1641M
12544 278,277,267M
12546 277,267,278M
12547 278,267,277M
12548 277,278,267M
12549 267,278,277M
12550 278,267,277M
12551 267,270,268M
12555 277,267,278M
12556 277,278,267M
12557 278,267,277M
12558 278,267,277M
12559 278,268,267M
12560 267,277,278M
12561 267,278,277M
12576 278,267,277M
12577 267,278,277M
12578 278,268,267M
15732 1622M