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, 
    354756, 355713, 354511, 349842, 354972, 
    358872, 356249, 349973, 387057, 349661
  ) 
  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.00263

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "56.07"
    },
    "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": 40,
        "rows_produced_per_join": 40,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "48.07",
          "eval_cost": "8.00",
          "prefix_cost": "56.07",
          "data_read_per_join": "2K"
        },
        "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,354756,355713,354511,349842,354972,358872,356249,349973,387057,349661)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
349661 11.87000000
349683 2.45000000
349842 2.35000000
349973 3.47000000
351887 6.77000000
354511 10.17000000
354756 4.22000000
354972 33.75000000
355713 28.65000000
356249 8.13000000
356555 21.22000000
356656 9.83000000
357504 2.69000000
358872 10.34000000
359422 2.69000000
387057 0.00000000
415289 18.20000000
415302 12.14000000
427779 16.18000000
427787 20.02000000