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 (
    6149, 5685, 6148, 6150, 6151, 5721, 5722, 
    5723, 5724, 5725, 5726, 5944, 5945, 
    5946, 5947, 6119, 6120, 6121, 6122, 
    6123, 6124, 6125, 5948, 6128, 6127, 
    6126, 6129, 6130, 6131, 6132, 6133, 
    6134, 6135, 5952, 6138, 6137, 6136, 
    6139, 6140, 6141, 6142, 6143, 6026, 
    6025, 6027, 6028, 6029, 6030, 6031, 
    6032, 6033, 6034, 6035, 6036, 6037, 
    6038, 6039, 6040, 6041, 6042, 6043, 
    6044, 6045, 6046, 6047, 6048, 6049, 
    6050, 6051, 6052, 6053, 6054, 6055, 
    6056, 6057, 6058, 6059, 6069, 6070, 
    6071, 6094, 6095, 6110, 6111, 5683, 
    6152, 6153, 6154, 6155, 6156, 6157, 
    6158, 6159, 6164, 6165, 6166, 6167, 
    6168, 6169, 6170, 6171, 6172, 6173, 
    6174, 6175, 6176, 6177, 6178, 6179, 
    6180, 6181, 6182, 6183, 6184
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00502

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "254.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": 282,
            "rows_produced_per_join": 282,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (6149,5685,6148,6150,6151,5721,5722,5723,5724,5725,5726,5944,5945,5946,5947,6119,6120,6121,6122,6123,6124,6125,5948,6128,6127,6126,6129,6130,6131,6132,6133,6134,6135,5952,6138,6137,6136,6139,6140,6141,6142,6143,6026,6025,6027,6028,6029,6030,6031,6032,6033,6034,6035,6036,6037,6038,6039,6040,6041,6042,6043,6044,6045,6046,6047,6048,6049,6050,6051,6052,6053,6054,6055,6056,6057,6058,6059,6069,6070,6071,6094,6095,6110,6111,5683,6152,6153,6154,6155,6156,6157,6158,6159,6164,6165,6166,6167,6168,6169,6170,6171,6172,6173,6174,6175,6176,6177,6178,6179,6180,6181,6182,6183,6184))",
            "cost_info": {
              "read_cost": "127.21",
              "eval_cost": "28.20",
              "prefix_cost": "155.41",
              "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": 14,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "70.50",
              "eval_cost": "1.41",
              "prefix_cost": "254.11",
              "data_read_per_join": "44K"
            },
            "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
5683 423,585,166M
5685 423,585,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
5944 423,585,166M
5945 423,585,166M
5946 423,585,166M
5947 423,585,166M
5948 423,166,585M
5952 423,166,585M
6025 423,166,585M
6026 166,423,585M
6027 166,423,585M
6028 166,423,585M
6029 166,423,585M
6030 166,423,585M
6031 423,166,585M
6032 423,166,585M
6033 166,423,585M
6034 166,423,585M
6035 423,166,585M
6036 423,166,585M
6037 166,423,585M
6038 166,423,585M
6039 423,166,585M
6040 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
6048 423,166,585M
6049 166,423,585M
6050 166,423,585M
6051 423,166,585M
6052 423,166,585M
6053 423,166,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
6069 423,166,585M
6070 423,166,585M
6071 423,166,585M
6094 423,166,585M
6095 166,423,585M
6110 423,166,585M
6111 423,166,585M
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
6136 423,166,585M
6137 423,166,585M
6138 423,166,585M
6139 423,166,585M
6140 423,166,585M
6141 423,166,585M
6142 423,166,585M
6143 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
6156 423,166,585M
6157 423,166,585M
6158 423,166,585M
6159 423,166,585M
6164 423,166,585M
6165 423,166,585M
6166 423,166,585M
6167 423,166,585M
6168 423,166,585M
6169 423,166,585M
6170 423,166,585M
6171 423,166,585M
6172 423,166,585M
6173 423,166,585M
6174 423,166,585M
6175 423,166,585M
6176 423,166,585M
6177 423,166,585M
6178 423,166,585M
6179 423,166,585M
6180 423,166,585M
6181 423,166,585M
6182 423,166,585M
6183 423,166,585M
6184 423,166,585M