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 (
    265043, 265226, 265229, 265045, 265228, 
    265046, 265047, 265030, 265227, 265225, 
    265044, 265224, 265369, 265371, 265370, 
    265372
  ) 
  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.00132

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "44.86"
    },
    "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": 32,
        "rows_produced_per_join": 32,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "38.46",
          "eval_cost": "6.40",
          "prefix_cost": "44.86",
          "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 (265043,265226,265229,265045,265228,265046,265047,265030,265227,265225,265044,265224,265369,265371,265370,265372)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
265030 38.00000000
265043 38.00000000
265044 38.00000000
265045 38.00000000
265046 48.00000000
265047 28.00000000
265224 28.00000000
265225 28.00000000
265226 18.00000000
265227 28.00000000
265228 18.00000000
265229 48.00000000
265369 28.00000000
265370 28.00000000
265371 28.00000000
265372 38.00000000