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 (
    145, 277, 139, 282, 179, 280, 148, 144, 
    143, 141, 111, 147, 213, 150, 216, 149, 
    258, 146, 233, 193, 225, 129, 383, 131, 
    257, 135, 132, 134, 127, 260, 262
  ) 
  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.00071

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_product_prices.product_id",
      "temporary_table": {
        "table": {
          "table_name": "cscart_product_prices",
          "access_type": "ALL",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id"
          ],
          "rows": 152,
          "filtered": 16.52835274,
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (145,277,139,282,179,280,148,144,143,141,111,147,213,150,216,149,258,146,233,193,225,129,383,131,257,135,132,134,127,260,262) and cscart_product_prices.usergroup_id in (0,1)"
        }
      }
    }
  }
}