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 (
    149042, 149050, 149051, 149052, 149053, 
    149054, 149055, 149056, 149057, 149058, 
    149059, 149060, 149061, 149062, 149063, 
    149064, 149065, 149066, 149067, 149068, 
    149069, 149070, 149071, 149072, 149073, 
    149074, 149075, 149076, 149077, 149078, 
    149079, 149080, 149081, 149082, 149083, 
    149084, 149085, 149086, 149087, 149088, 
    149089, 149090, 149091, 149092, 149093, 
    149094, 149095, 149096, 149097, 149098, 
    149099
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00148

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "82.68"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "link_type",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 51,
            "rows_produced_per_join": 51,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "11.28",
              "eval_cost": "10.20",
              "prefix_cost": "21.48",
              "data_read_per_join": "816"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`webmarco`.`cscart_products_categories`.`product_id` in (149042,149050,149051,149052,149053,149054,149055,149056,149057,149058,149059,149060,149061,149062,149063,149064,149065,149066,149067,149068,149069,149070,149071,149072,149073,149074,149075,149076,149077,149078,149079,149080,149081,149082,149083,149084,149085,149086,149087,149088,149089,149090,149091,149092,149093,149094,149095,149096,149097,149098,149099))"
          }
        },
        {
          "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": [
              "webmarco.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "51.00",
              "eval_cost": "0.51",
              "prefix_cost": "82.68",
              "data_read_per_join": "15K"
            },
            "used_columns": [
              "category_id",
              "usergroup_ids",
              "status",
              "storefront_id"
            ],
            "attached_condition": "((`webmarco`.`cscart_categories`.`storefront_id` in (0,1)) and ((`webmarco`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`webmarco`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`webmarco`.`cscart_categories`.`usergroup_ids`)) and (`webmarco`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
149042 2904M
149050 2904M
149051 2904M
149052 2904M
149053 2904M
149054 2904M
149055 2904M
149056 2904M
149057 2904M
149058 2904M
149059 2904M
149060 2904M
149061 2904M
149062 2904M
149063 2904M
149064 2904M
149065 2904M
149066 2904M
149067 2904M
149068 2904M
149069 2904M
149070 2904M
149071 2904M
149072 2904M
149073 2904M
149074 2904M
149075 2904M
149076 2904M
149077 2904M
149078 2904M
149079 2904M
149080 2904M
149081 2904M
149082 2904M
149083 2904M
149084 2904M
149085 2904M
149086 2904M
149087 2904M
149088 2904M
149089 2904M
149090 2904M
149091 2904M
149092 2904M
149093 2904M
149094 2904M
149095 2904M
149096 2904M
149097 2904M
149098 2904M
149099 2904M