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 (
    7379, 10086, 11141, 8573, 6941, 3871, 
    4296, 4352, 4885, 5185, 5315, 5353, 
    6485, 7121, 3786, 4237, 5199, 5308, 
    5312, 5326
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00223

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "40.21"
    },
    "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": 44,
            "rows_produced_per_join": 44,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (7379,10086,11141,8573,6941,3871,4296,4352,4885,5185,5315,5353,6485,7121,3786,4237,5199,5308,5312,5326))",
            "cost_info": {
              "read_cost": "20.41",
              "eval_cost": "4.40",
              "prefix_cost": "24.81",
              "data_read_per_join": "704"
            },
            "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": "11.00",
              "eval_cost": "0.22",
              "prefix_cost": "40.21",
              "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
3786 423,166,448M
3871 166,423,448M
4237 219,494M
4296 513,166,515M
4352 513,166,515M
4885 329,166,457M
5185 329,166,347M
5199 328,402,407,359,360,166,250,412M
5308 372,552,550M
5312 372,552,550M
5315 372,552,550M
5326 372,552,550M
5353 372,552,550M
6485 578M
6941 630M
7121 630M
7379 651M
8573 658M
10086 167M
11141 664M