SELECT 
  cscart_product_prices.product_id, 
  MIN(
    IF(
      cscart_product_prices.percentage_discount = 0, 
      cscart_product_prices.price, 
      cscart_product_prices.price - (
        cscart_product_prices.price * cscart_product_prices.percentage_discount
      )/ 100
    )
  ) AS price 
FROM 
  cscart_product_prices 
WHERE 
  cscart_product_prices.product_id IN (
    415289, 427787, 415302, 427779, 349683, 
    356555, 359422, 357504, 351887, 356656, 
    355713, 354511, 354756, 349842, 354972, 
    358872, 356249, 349973, 387057, 349661, 
    351227, 352595, 354503, 349663, 350115, 
    387058, 349883, 352200, 352526, 351878, 
    351863, 349776, 351988, 352081, 352520, 
    351867, 352070, 352076, 351913, 352066, 
    358690, 351846, 355628, 351888, 355778, 
    349662, 349657, 351884, 350003, 351843, 
    352418, 355851, 351855, 351869, 352693, 
    351849, 351852, 352566, 350196, 351741, 
    352074, 387065, 352500, 351847, 352150, 
    349686, 351868, 351993, 356187, 351278, 
    351918, 351925, 351839, 351919, 351870, 
    352017, 352038, 358758, 355894, 351858, 
    349656, 351996, 352684, 352033, 351225, 
    352375, 349674, 351907, 356543, 351253, 
    351881, 352314, 352067, 358870, 351429, 
    351836, 351914, 351748, 349650, 357134
  ) 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0, 1) 
GROUP BY 
  cscart_product_prices.product_id

Query time 0.00186

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "280.31"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "usergroup",
        "used_key_parts": [
          "product_id",
          "usergroup_id",
          "lower_limit"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 200,
        "rows_produced_per_join": 200,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "240.31",
          "eval_cost": "40.00",
          "prefix_cost": "280.31",
          "data_read_per_join": "12K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`webmarco`.`cscart_product_prices`.`lower_limit` = 1) and (`webmarco`.`cscart_product_prices`.`product_id` in (415289,427787,415302,427779,349683,356555,359422,357504,351887,356656,355713,354511,354756,349842,354972,358872,356249,349973,387057,349661,351227,352595,354503,349663,350115,387058,349883,352200,352526,351878,351863,349776,351988,352081,352520,351867,352070,352076,351913,352066,358690,351846,355628,351888,355778,349662,349657,351884,350003,351843,352418,355851,351855,351869,352693,351849,351852,352566,350196,351741,352074,387065,352500,351847,352150,349686,351868,351993,356187,351278,351918,351925,351839,351919,351870,352017,352038,358758,355894,351858,349656,351996,352684,352033,351225,352375,349674,351907,356543,351253,351881,352314,352067,358870,351429,351836,351914,351748,349650,357134)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}