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 (
    4108, 4107, 3734, 4109, 3798, 4110, 4111, 
    3811, 4899, 4898, 4897, 4896, 4894, 
    4915, 4917, 4924, 4923, 4922, 4921, 
    4920, 4919, 4918, 4925, 4927, 4926, 
    4941, 4940, 4939, 4935, 4973, 4972, 
    4971, 4970, 4969, 4968, 4967, 4966, 
    4965, 4964, 4963, 4962, 4987, 4986, 
    4985, 4984, 4977, 4976, 4981, 4980, 
    4979, 4978, 4982, 4983, 5618, 5619, 
    5620, 5621, 5687, 5688, 5689, 5690, 
    5691, 5692, 5693, 5694, 5696, 5697, 
    5698, 5699, 5700, 5701, 5702, 5703, 
    5704, 5705, 6204, 5894, 5893, 5892, 
    6064, 6065, 6066, 6067, 6074, 6075, 
    5956, 6087, 6088, 6089, 6090, 6091, 
    6092, 6093, 6117, 6118, 6196, 6195, 
    6194, 6197, 6198, 6199, 6200, 6201, 
    6202
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00367

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "230.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": 256,
            "rows_produced_per_join": 256,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (4108,4107,3734,4109,3798,4110,4111,3811,4899,4898,4897,4896,4894,4915,4917,4924,4923,4922,4921,4920,4919,4918,4925,4927,4926,4941,4940,4939,4935,4973,4972,4971,4970,4969,4968,4967,4966,4965,4964,4963,4962,4987,4986,4985,4984,4977,4976,4981,4980,4979,4978,4982,4983,5618,5619,5620,5621,5687,5688,5689,5690,5691,5692,5693,5694,5696,5697,5698,5699,5700,5701,5702,5703,5704,5705,6204,5894,5893,5892,6064,6065,6066,6067,6074,6075,5956,6087,6088,6089,6090,6091,6092,6093,6117,6118,6196,6195,6194,6197,6198,6199,6200,6201,6202))",
            "cost_info": {
              "read_cost": "115.61",
              "eval_cost": "25.60",
              "prefix_cost": "141.21",
              "data_read_per_join": "4K"
            },
            "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": 12,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "64.00",
              "eval_cost": "1.28",
              "prefix_cost": "230.81",
              "data_read_per_join": "40K"
            },
            "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
3734 423,166,449M
3798 166,423,449M
3811 423,449,166M
4107 423,166,449M
4108 166,423,449M
4109 423,166,449M
4110 423,449,166M
4111 449,423,166M
4894 166,423,449M
4896 166,423,449M
4897 166,423,449M
4898 166,423,449M
4899 166,423,449M
4915 166,423,449M
4917 166,423,449M
4918 166,423,449M
4919 166,423,449M
4920 166,423,449M
4921 166,423,449M
4922 166,423,449M
4923 166,423,449M
4924 166,423,449M
4925 166,423,449M
4926 166,423,449M
4927 166,423,449M
4935 166,423,449M
4939 166,423,449M
4940 166,423,449M
4941 166,423,449M
4962 166,423,449M
4963 166,423,449M
4964 166,423,449M
4965 166,423,449M
4966 166,423,449M
4967 166,423,449M
4968 166,423,449M
4969 166,423,449M
4970 166,423,449M
4971 166,423,449M
4972 166,423,449M
4973 166,423,449M
4976 166,423,449M
4977 166,423,449M
4978 166,423,449M
4979 166,423,449M
4980 166,423,449M
4981 166,423,449M
4982 166,423,449M
4983 166,423,449M
4984 166,423,449M
4985 166,423,449M
4986 166,423,449M
4987 166,423,449M
5618 423,449,166M
5619 423,449,166M
5620 423,449,166M
5621 423,449,166M
5687 423,449,166M
5688 423,449,166M
5689 423,449,166M
5690 423,449,166M
5691 423,449,166M
5692 423,449,166M
5693 423,449,166M
5694 423,449,166M
5696 423,449,166M
5697 423,449,166M
5698 423,449,166M
5699 423,449,166M
5700 423,166,449M
5701 423,166,449M
5702 423,166,449M
5703 423,449,166M
5704 423,449,166M
5705 423,449,166M
5892 423,449,166M
5893 423,449,166M
5894 423,449,166M
5956 166,423,449M
6064 423,166,449M
6065 423,166,449M
6066 423,166,449M
6067 423,166,449M
6074 423,166,449M
6075 423,166,449M
6087 166,423,449M
6088 166,423,449M
6089 166,423,449M
6090 166,423,449M
6091 423,166,449M
6092 166,423,449M
6093 423,166,449M
6117 423,166,449M
6118 423,166,449M
6194 423,166,449M
6195 423,166,449M
6196 166,423,449M
6197 166,423,449M
6198 166,423,449M
6199 423,166,449M
6200 423,166,449M
6201 423,166,449M
6202 423,166,449M
6204 423,166,449M