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 (
    111, 51212, 144, 51215, 51214, 51213, 
    51444, 51445, 51446, 51447, 51220, 
    51219, 51218, 51217, 51216, 146, 51448, 
    51449, 51231, 51230, 51229, 51228, 
    51227, 51226, 51225, 109, 51243, 51242, 
    51241, 51240, 51239, 36, 51253, 51252, 
    51251, 51250, 51249, 34, 51272, 51271, 
    51270, 51269, 51268, 51267, 51266, 
    145, 51273, 147, 51277, 51276, 51275, 
    51274
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00196

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "155.41"
    },
    "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": 178,
            "rows_produced_per_join": 178,
            "filtered": "100.00",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (111,51212,144,51215,51214,51213,51444,51445,51446,51447,51220,51219,51218,51217,51216,146,51448,51449,51231,51230,51229,51228,51227,51226,51225,109,51243,51242,51241,51240,51239,36,51253,51252,51251,51250,51249,34,51272,51271,51270,51269,51268,51267,51266,145,51273,147,51277,51276,51275,51274))",
            "cost_info": {
              "read_cost": "75.31",
              "eval_cost": "17.80",
              "prefix_cost": "93.11",
              "data_read_per_join": "2K"
            },
            "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": [
              "test_uchur_k.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 8,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "44.50",
              "eval_cost": "0.89",
              "prefix_cost": "155.41",
              "data_read_per_join": "47K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`test_uchur_k`.`cscart_categories`.`storefront_id` in (0,1)) and ((`test_uchur_k`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`test_uchur_k`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`test_uchur_k`.`cscart_categories`.`usergroup_ids`))) and (`test_uchur_k`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
34 42,7,40M
36 43,7,40M
109 42,7,40M
111 7,42,40M
144 42,7,390,40M
145 42,7,390,40M
146 42,7,390,40M
147 7,42,40M
51212 42,7,40M
51213 390,42,7,40M
51214 390,42,7,40M
51215 390,42,7,40M
51216 390,42,7,40M
51217 390,42,7,40M
51218 390,42,7,40M
51219 390,42,7,40M
51220 390,42,7,40M
51225 7,42,40M
51226 7,42,40M
51227 7,42,40M
51228 7,42,40M
51229 7,42,40M
51230 7,42,40M
51231 7,42,40M
51239 7,43,40M
51240 7,43,40M
51241 7,43,40M
51242 7,43,40M
51243 7,43,40M
51249 7,42,40M
51250 7,42,40M
51251 7,42,40M
51252 7,42,40M
51253 7,42,40M
51266 390,42,7,40M
51267 390,7,42,40M
51268 390,42,7,40M
51269 42,7,390,40M
51270 7,390,42,40M
51271 390,42,7,40M
51272 42,390,7,40M
51273 7,42,40M
51274 42,7,40M
51275 42,7,40M
51276 42,7,40M
51277 42,7,40M
51444 390,7,42,40M
51445 390,7,42,40M
51446 390,7,42,40M
51447 390,7,42,40M
51448 390,7,42,40M
51449 390,7,42,40M