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 (
    388730, 388731, 388732, 388733, 388734, 
    388735, 388736, 388737, 388738, 388739, 
    388741, 388742, 388743, 388744, 388745, 
    388746, 388748, 388749, 388750, 388751
  ) 
  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.00099

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 (388730,388731,388732,388733,388734,388735,388736,388737,388738,388739,388741,388742,388743,388744,388745,388746,388748,388749,388750,388751)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
388730 80.48000000
388731 82.95000000
388732 84.19000000
388733 123.81000000
388734 96.57000000
388735 101.52000000
388736 95.33000000
388737 84.19000000
388738 94.10000000
388739 141.14000000
388741 130.00000000
388742 144.86000000
388743 128.76000000
388744 151.05000000
388745 198.10000000
388746 184.48000000
388748 120.10000000
388749 158.48000000
388750 136.19000000
388751 128.76000000