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 (
    388363, 388361, 388366, 388370, 388451, 
    389024, 389305, 388693, 389395, 389255, 
    388526, 388500, 389794, 389795, 381748, 
    381733, 435251, 429104, 388585, 388674
  ) 
  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.00152

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 (388363,388361,388366,388370,388451,389024,389305,388693,389395,389255,388526,388500,389794,389795,381748,381733,435251,429104,388585,388674)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
381733 5000.00000000
381748 7000.00000000
388361 66.00000000
388363 77.00000000
388366 77.00000000
388370 104.50000000
388451 49.52000000
388500 61.90000000
388526 100.29000000
388585 102.76000000
388674 220.38000000
388693 220.00000000
389024 49.52000000
389255 319.00000000
389305 627.00000000
389395 71.50000000
389794 188.95000000
389795 163.05000000
429104 80.00000000
435251 275.00000000