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 (
    6925, 6946, 6953, 6954, 6960, 6962, 6972, 
    7016, 7056, 7088, 7112, 7123, 7135, 
    7137, 7145, 7149, 7150, 7178, 7180, 
    7205, 7296, 7299, 6875, 6884, 6895, 
    6965, 6973, 6999, 7001, 7057, 7091, 
    7103, 7163, 7187, 7188, 7199, 7304, 
    7313, 6916, 6922
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00103

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "42.01"
    },
    "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": 40,
            "rows_produced_per_join": 40,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (6925,6946,6953,6954,6960,6962,6972,7016,7056,7088,7112,7123,7135,7137,7145,7149,7150,7178,7180,7205,7296,7299,6875,6884,6895,6965,6973,6999,7001,7057,7091,7103,7163,7187,7188,7199,7304,7313,6916,6922))",
            "cost_info": {
              "read_cost": "24.01",
              "eval_cost": "4.00",
              "prefix_cost": "28.01",
              "data_read_per_join": "640"
            },
            "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.00",
              "eval_cost": "0.20",
              "prefix_cost": "42.01",
              "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
6875 628M
6884 628M
6895 628M
6916 628M
6922 628M
6925 628M
6946 628M
6953 628M
6954 628M
6960 628M
6962 628M
6965 628M
6972 628M
6973 628M
6999 628M
7001 628M
7016 628M
7056 628M
7057 628M
7088 628M
7091 628M
7103 628M
7112 628M
7123 628M
7135 628M
7137 628M
7145 628M
7149 628M
7150 628M
7163 628M
7178 628M
7180 628M
7187 628M
7188 628M
7199 628M
7205 628M
7296 628M
7299 628M
7304 628M
7313 628M