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 (
    6340, 6385, 6406, 6344, 6380, 6401, 8512, 
    8513, 8510, 4949, 8511, 6459, 8717, 
    6383, 6407, 6451, 8716, 8711, 8712, 
    6381
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00187

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "69.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": 81,
            "rows_produced_per_join": 81,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (6340,6385,6406,6344,6380,6401,8512,8513,8510,4949,8511,6459,8717,6383,6407,6451,8716,8711,8712,6381))",
            "cost_info": {
              "read_cost": "33.36",
              "eval_cost": "8.10",
              "prefix_cost": "41.46",
              "data_read_per_join": "1K"
            },
            "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": 4,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "20.25",
              "eval_cost": "0.41",
              "prefix_cost": "69.81",
              "data_read_per_join": "12K"
            },
            "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
4949 372,166,453,174,454,190M
6340 566,372,595M
6344 566,372,595M
6380 372,174,453,329,166,337,455,527M
6381 372,174,453,329,166,337,455,527M
6383 372,583M
6385 372,583M
6401 372,583M
6406 372,583M
6407 566,372,595M
6451 174,453,372,166,454,190M
6459 174,453,372,166,190,454M
8510 454,174,166,372,453,190M
8511 454,166,174,453,372,190M
8512 174,453,454,166,372,190M
8513 453,174,372,454,166,190M
8711 166,453,190,372,174,454M
8712 166,453,190,372,174,454M
8716 372,166,174,454,453,190M
8717 372,454,453,166,174,190M