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 (
    5504, 5505, 5506, 5507, 5644, 5645, 5647, 
    5648, 5651, 5654, 6081, 6082, 6114, 
    6115, 6146, 6147, 6185, 6186, 6187, 
    6188, 6297, 6298, 6300, 6299, 6304, 
    6303, 6306, 6305, 6749, 6750
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00199

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "65.11"
    },
    "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": 72,
            "rows_produced_per_join": 72,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (5504,5505,5506,5507,5644,5645,5647,5648,5651,5654,6081,6082,6114,6115,6146,6147,6185,6186,6187,6188,6297,6298,6300,6299,6304,6303,6306,6305,6749,6750))",
            "cost_info": {
              "read_cost": "32.71",
              "eval_cost": "7.20",
              "prefix_cost": "39.91",
              "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": 3,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "18.00",
              "eval_cost": "0.36",
              "prefix_cost": "65.11",
              "data_read_per_join": "11K"
            },
            "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
5504 522,521,535M
5505 522,521,535M
5506 522,521,535M
5507 522,521,535M
5644 522,521,535M
5645 522,521,535M
5647 522,521,536M
5648 522,521,536M
5651 522,521,536M
5654 522,521,536M
6081 521,522,535M
6082 522,521,535M
6114 521,522,536M
6115 522,521,536M
6146 521,522,536M
6147 522,521,536M
6185 522,521,536M
6186 522,521,536M
6187 521,522,536M
6188 522,521,536M
6297 521,522,535M
6298 522,521,535M
6299 521,522,536M
6300 521,522,536M
6303 521,522,535M
6304 522,521,535M
6305 521,522,535M
6306 521,522,535M
6749 590M
6750 590M