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 (
    329, 330, 331, 333, 334, 335, 337, 338, 
    339, 341, 342, 343, 371, 372, 373, 374, 
    375, 376, 377, 378
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00102

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "36.06"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "0.51"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 84,
            "rows_produced_per_join": 3,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "19.53",
              "eval_cost": "0.67",
              "prefix_cost": "20.20",
              "data_read_per_join": "8K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`pankajecarter_systemfour`.`cscart_categories`.`storefront_id` in (0,1)) and ((`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`)) and (`pankajecarter_systemfour`.`cscart_categories`.`status` in ('A','H')))"
          }
        },
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "pankajecarter_systemfour.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 3,
            "rows_produced_per_join": 0,
            "filtered": "5.04",
            "index_condition": "(`pankajecarter_systemfour`.`cscart_products_categories`.`product_id` in (329,330,331,333,334,335,337,338,339,341,342,343,371,372,373,374,375,376,377,378))",
            "cost_info": {
              "read_cost": "13.34",
              "eval_cost": "0.10",
              "prefix_cost": "35.55",
              "data_read_per_join": "8"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
329 235M
330 235M
331 235M
333 235M
334 235M
335 235M
337 235M
338 235M
339 235M
341 235M
342 235M
343 235M
371 235M
372 235M
373 235M
374 235M
375 235M
376 235M
377 235M
378 235M