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 (
    370, 3844, 3854, 3853, 3852, 3851, 3850, 
    3849, 3848, 3847, 3846, 3845, 3843, 
    3856, 3785, 3841, 3840, 3839, 3838, 
    3837, 3836, 3835, 3834, 3833, 3832, 
    3855, 3857, 3830, 3801, 3882, 3881, 
    3880, 3806, 3805, 3877, 3808, 3807, 
    3874, 3873, 3812, 3870, 3815, 3814, 
    3813, 3867, 3866, 3865, 3864, 3863, 
    3862, 3861, 3824, 3859, 3831, 3829, 
    3773, 3786, 3797, 3796, 3795, 3794, 
    3793, 3792, 3791, 3790, 3789, 3788, 
    3787, 3842, 3799, 3782, 3781, 3780, 
    3779, 3778, 3777, 3776, 3775, 3774, 
    3798, 3800, 3828, 3860, 3827, 3858, 
    3823, 3822, 3821, 3820, 3819, 3818, 
    3817, 3816, 3869, 3868, 3872, 3871, 
    3811, 3810, 3809, 3876, 3875, 3879, 
    3878, 3804, 3803, 3802, 3883, 3884
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.03073

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cscart_products_categories.product_id",
        "table": {
          "table_name": "cscart_products_categories",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "pt"],
          "rows": 14910,
          "filtered": 39.014,
          "attached_condition": "cscart_products_categories.product_id in (370,3844,3854,3853,3852,3851,3850,3849,3848,3847,3846,3845,3843,3856,3785,3841,3840,3839,3838,3837,3836,3835,3834,3833,3832,3855,3857,3830,3801,3882,3881,3880,3806,3805,3877,3808,3807,3874,3873,3812,3870,3815,3814,3813,3867,3866,3865,3864,3863,3862,3861,3824,3859,3831,3829,3773,3786,3797,3796,3795,3794,3793,3792,3791,3790,3789,3788,3787,3842,3799,3782,3781,3780,3779,3778,3777,3776,3775,3774,3798,3800,3828,3860,3827,3858,3823,3822,3821,3820,3819,3818,3817,3816,3869,3868,3872,3871,3811,3810,3809,3876,3875,3879,3878,3804,3803,3802,3883,3884)"
        }
      }
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["herbaria_cscart_4_18.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')"
    }
  }
}