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 (
    2227, 
    2226, 
    2430, 
    2431, 
    2522, 
    2655, 
    2698, 
    2702, 
    2703, 
    2719, 
    2728, 
    2805, 
    2808, 
    2922, 
    2923, 
    2924, 
    2925, 
    3112, 
    3113, 
    3114, 
    3115, 
    3150, 
    3151, 
    3153, 
    3155, 
    3156, 
    3213, 
    3214, 
    3216, 
    3215, 
    2737, 
    3255, 
    3289, 
    3377, 
    3378, 
    3382, 
    3383, 
    3385, 
    3386, 
    3552, 
    3553, 
    3604, 
    3603, 
    3605, 
    3606, 
    3664, 
    3665, 
    3832, 
    3831, 
    3834, 
    3835, 
    4024, 
    3649, 
    4026, 
    4025, 
    4110, 
    4111, 
    3811, 
    4899, 
    4898, 
    4897, 
    4896, 
    4894, 
    4915, 
    4917, 
    4924, 
    4923, 
    4922, 
    4921, 
    4920, 
    4919, 
    4918, 
    4925, 
    4927, 
    4926, 
    4944, 
    4942, 
    4945, 
    4951, 
    4953, 
    4959, 
    4958, 
    4973, 
    4972, 
    4971, 
    4970, 
    4969, 
    4968, 
    4967, 
    4966, 
    4965, 
    4964, 
    4963, 
    4962, 
    4987, 
    4986, 
    4985, 
    4984, 
    4977, 
    4976, 
    4981, 
    4980, 
    4979, 
    4978, 
    4982, 
    4983, 
    5117, 
    5116, 
    5114, 
    5618, 
    5619, 
    5620, 
    5621, 
    6149, 
    5685, 
    6148, 
    6150, 
    6151, 
    5687, 
    5688, 
    5689, 
    5690, 
    5691, 
    5692, 
    5693, 
    5694, 
    5696, 
    5697, 
    5698, 
    5699, 
    5703, 
    5704, 
    5705, 
    5721, 
    5722, 
    5723, 
    5724, 
    5725, 
    5726, 
    5894, 
    5893, 
    5892, 
    5934, 
    5935, 
    5944, 
    5945, 
    5946, 
    5947, 
    6119, 
    6120, 
    6121, 
    6122, 
    6123, 
    6124, 
    6125, 
    5948, 
    6128, 
    6127, 
    6126, 
    6129, 
    6130, 
    6131, 
    6132, 
    6133, 
    6134, 
    6135, 
    6026, 
    6025, 
    6027, 
    6028, 
    6029, 
    6030, 
    6041, 
    6042, 
    6043, 
    6044, 
    6045, 
    6046, 
    6047, 
    6054, 
    6055, 
    6056, 
    6057, 
    6058, 
    6059, 
    5956, 
    6087, 
    6088, 
    6089, 
    6090, 
    6091, 
    6092, 
    6093, 
    6098, 
    6100, 
    5683, 
    6152, 
    6153, 
    6154, 
    6155, 
    6196, 
    6195, 
    6194, 
    6197, 
    6198, 
    6199, 
    6200, 
    6201, 
    6202
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00593

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "386.66"
    },
    "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": 418,
            "rows_produced_per_join": 418,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (2227,2226,2430,2431,2522,2655,2698,2702,2703,2719,2728,2805,2808,2922,2923,2924,2925,3112,3113,3114,3115,3150,3151,3153,3155,3156,3213,3214,3216,3215,2737,3255,3289,3377,3378,3382,3383,3385,3386,3552,3553,3604,3603,3605,3606,3664,3665,3832,3831,3834,3835,4024,3649,4026,4025,4110,4111,3811,4899,4898,4897,4896,4894,4915,4917,4924,4923,4922,4921,4920,4919,4918,4925,4927,4926,4944,4942,4945,4951,4953,4959,4958,4973,4972,4971,4970,4969,4968,4967,4966,4965,4964,4963,4962,4987,4986,4985,4984,4977,4976,4981,4980,4979,4978,4982,4983,5117,5116,5114,5618,5619,5620,5621,6149,5685,6148,6150,6151,5687,5688,5689,5690,5691,5692,5693,5694,5696,5697,5698,5699,5703,5704,5705,5721,5722,5723,5724,5725,5726,5894,5893,5892,5934,5935,5944,5945,5946,5947,6119,6120,6121,6122,6123,6124,6125,5948,6128,6127,6126,6129,6130,6131,6132,6133,6134,6135,6026,6025,6027,6028,6029,6030,6041,6042,6043,6044,6045,6046,6047,6054,6055,6056,6057,6058,6059,5956,6087,6088,6089,6090,6091,6092,6093,6098,6100,5683,6152,6153,6154,6155,6196,6195,6194,6197,6198,6199,6200,6201,6202))",
            "cost_info": {
              "read_cost": "198.56",
              "eval_cost": "41.80",
              "prefix_cost": "240.36",
              "data_read_per_join": "6K"
            },
            "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": 20,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "104.50",
              "eval_cost": "2.09",
              "prefix_cost": "386.66",
              "data_read_per_join": "65K"
            },
            "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
2226 424,423,450,166M
2227 423,424,450,166M
2430 423,424,450,166M
2431 423,424,450,166M
2522 423,424,452,166M
2655 423,424,450,166M
2698 450,424,423,166M
2702 423,424,450,166M
2703 423,424,450,166M
2719 423,424,450,166M
2728 423,424,450,166M
2737 423,424,451,166M
2805 424,423,492,166M
2808 423,424,492,166M
2922 423,424,450,166M
2923 423,424,450,166M
2924 423,424,451,166M
2925 423,424,451,166M
3112 423,424,452,166M
3113 423,424,452,166M
3114 492,452,424,423,166M
3115 423,424,452,492,166M
3150 423,424,450,166M
3151 423,424,450,166M
3153 423,451,424,166M
3155 423,424,451,166M
3156 423,424,451,166M
3213 450,423,424,166M
3214 450,424,423,166M
3215 450,424,423,166M
3216 450,423,424,166M
3255 423,424,451,166M
3289 450,424,423,166M
3377 492,424,423,166M
3378 492,423,424,166M
3382 423,424,450,166M
3383 423,424,450,166M
3385 423,424,450,166M
3386 423,424,450,166M
3552 423,424,450,166M
3553 423,424,450,166M
3603 451,424,423,166M
3604 424,423,451,166M
3605 424,423,166,451M
3606 166,424,423,451M
3649 423,492,166M
3664 423,424,450,166M
3665 423,424,450,166M
3811 423,449,166M
3831 423,424,450,166M
3832 424,450,423,166M
3834 423,424,450,166M
3835 423,424,450,166M
4024 423,492,166M
4025 423,492,166M
4026 423,492,166M
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
4942 423,520,166M
4944 423,520,166M
4945 423,520,166M
4951 423,520,166M
4953 423,520,166M
4958 423,166,520M
4959 423,166,520M
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
5114 450,423,424,166M
5116 450,423,424,166M
5117 450,423,424,166M
5618 423,449,166M
5619 423,449,166M
5620 423,449,166M
5621 423,449,166M
5683 423,585,166M
5685 423,585,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
5703 423,449,166M
5704 423,449,166M
5705 423,449,166M
5721 423,585,166M
5722 423,585,166M
5723 423,585,166M
5724 423,585,166M
5725 423,585,166M
5726 423,585,166M
5892 423,449,166M
5893 423,449,166M
5894 423,449,166M
5934 250,308,350,423,166,386,382M
5935 423,166,350,386,308,250,382M
5944 423,585,166M
5945 423,585,166M
5946 423,585,166M
5947 423,585,166M
5948 423,166,585M
5956 166,423,449M
6025 423,166,585M
6026 166,423,585M
6027 166,423,585M
6028 166,423,585M
6029 166,423,585M
6030 166,423,585M
6041 423,166,585M
6042 423,166,585M
6043 423,166,585M
6044 423,166,585M
6045 423,166,585M
6046 423,166,585M
6047 166,423,585M
6054 423,166,585M
6055 166,423,585M
6056 423,166,585M
6057 423,166,585M
6058 423,166,585M
6059 423,166,585M
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
6098 166,423,586M
6100 166,423,586M
6119 423,585,166M
6120 423,585,166M
6121 423,585,166M
6122 423,585,166M
6123 423,585,166M
6124 423,585,166M
6125 423,585,166M
6126 423,166,585M
6127 423,166,585M
6128 423,166,585M
6129 423,166,585M
6130 423,166,585M
6131 423,166,585M
6132 423,166,585M
6133 423,166,585M
6134 423,166,585M
6135 423,166,585M
6148 423,585,166M
6149 423,585,166M
6150 423,585,166M
6151 423,585,166M
6152 423,585,166M
6153 423,585,166M
6154 423,585,166M
6155 423,585,166M
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