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 (
    15747, 15748, 15751, 15752, 15753, 15754, 
    15755, 15756, 16334, 16335, 16338, 
    16339, 16340, 16344, 16345, 16348, 
    16349, 16350, 16351, 16352, 16353
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00086

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.245842351,
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_products_categories",
          "access_type": "range",
          "possible_keys": ["PRIMARY", "pt"],
          "key": "pt",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "loops": 1,
          "rows": 132,
          "cost": 0.03915306,
          "filtered": 100,
          "attached_condition": "cscart_products_categories.product_id in (15747,15748,15751,15752,15753,15754,15755,15756,16334,16335,16338,16339,16340,16344,16345,16348,16349,16350,16351,16352,16353)",
          "using_index": true
        }
      },
      {
        "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": ["u508912950_dev_2025.cscart_products_categories.category_id"],
          "loops": 132,
          "rows": 1,
          "cost": 0.12134288,
          "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
15747 1397,1401,1613,1614,1620,1671,1617M
15748 1397,1401,1613,1614,1620,1671,1617M
15751 925,1396,1398,1411,1613M
15752 925,1396,1398,1411,1613M
15753 1397,1613,1614,1620,1671,1617M
15754 1397,1613,1614,1620,1671,1617M
15755 1397,1613,1614,1620,1671,1617M
15756 1397,1613,1614,1620,1671,1617M
16334 925,1125,1670,1396M
16335 925,1125,1670,1396M
16338 925,1125,1397,1613,1620,1670,1671,1396M
16339 925,1125,1397,1613,1620,1670,1671,1396M
16340 925,1125,1397,1613,1620,1670,1671,1396M
16344 925,1125,1397,1613,1620,1670,1671,1396M
16345 925,1125,1397,1613,1620,1670,1671,1396M
16348 1397,1613,1614,1620,1671,1617M
16349 1397,1613,1614,1620,1671,1617M
16350 1397,1613,1614,1620,1671,1617M
16351 1397,1613,1614,1620,1671,1617M
16352 1397,1613,1614,1620,1671,1617M
16353 1397,1613,1614,1620,1671,1617M