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 (
    2459, 284, 328, 138, 144, 107, 2507, 1140, 
    136, 141, 285, 1219, 120, 185, 2509, 
    142, 140, 927, 134, 630, 137, 2508, 310, 
    287, 294, 133, 1488, 2495, 143, 329, 
    187, 186
  ) 
  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.00135

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 32,
      "filtered": 5.088717937,
      "index_condition": "cscart_product_prices.product_id in (2459,284,328,138,144,107,2507,1140,136,141,285,1219,120,185,2509,142,140,927,134,630,137,2508,310,287,294,133,1488,2495,143,329,187,186)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
107 10.7000000000000000
120 9.6300000000000000
133 80.2500000000000000
134 24.6100000000000000
136 32.1000000000000000
137 749.0000000000000000
138 0.6600000000000000
140 11.7700000000000000
141 100.0000000000000000
142 100.0000000000000000
143 100.0000000000000000
144 100.0000000000000000
185 2.1400000000000000
186 2.1400000000000000
187 2.3500000000000000
284 0.3700000000000000
285 0.3700000000000000
287 11.7700000000000000
294 32.1000000000000000
310 53.5000000000000000
328 105.0000000000000000
329 0.1100000000000000
630 0.2100000000000000
927 0.0400000000000000
1140 0.4400000000000000
1219 535.0000000000000000
1488 0.9900000000000000
2459 37.4500000000000000
2495 32.0900000000000000
2507 89.0000000000000000
2508 89.0000000000000000
2509 0.0010700000000000