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 (
    10381, 10663, 10954, 11408, 6814, 6984, 
    7890, 10198, 6358, 6365, 6979, 6982, 
    7119, 7397, 7450, 7454, 7455, 7456, 
    7497, 7524, 7594, 7603, 7642, 7644
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00168

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "38.81"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 41,
            "rows_produced_per_join": 41,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (10381,10663,10954,11408,6814,6984,7890,10198,6358,6365,6979,6982,7119,7397,7450,7454,7455,7456,7497,7524,7594,7603,7642,7644))",
            "cost_info": {
              "read_cost": "20.36",
              "eval_cost": "4.10",
              "prefix_cost": "24.46",
              "data_read_per_join": "656"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "10.25",
              "eval_cost": "0.21",
              "prefix_cost": "38.81",
              "data_read_per_join": "6K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`storefront_id` in (0,1)) and ((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`))) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
6358 538,521,539M
6365 538,521,539M
6814 466,574M
6979 617M
6982 617M
6984 617M
7119 630M
7397 651M
7450 651M
7454 651M
7455 651M
7456 651M
7497 623M
7524 556,554M
7594 623M
7603 466M
7642 556M
7644 575M
7890 623,622,625M
10198 372,454,453,190,174,166M
10381 575M
10663 619M
10954 560,562M
11408 664M