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 (
    280, 689, 281, 283, 286, 287, 501, 289, 
    288, 291, 483, 293, 294, 295, 296, 641, 
    298, 299, 300, 577, 301, 302, 646, 498, 
    304, 306, 307, 514
  ) 
  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.00388

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": 56,
      "filtered": 100,
      "index_condition": "cscart_product_prices.product_id in (280,689,281,283,286,287,501,289,288,291,483,293,294,295,296,641,298,299,300,577,301,302,646,498,304,306,307,514)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
280 6420.00000000
281 4530.00000000
283 4630.00000000
286 3760.00000000
287 5760.00000000
288 5760.00000000
289 5760.00000000
291 5760.00000000
293 8300.00000000
294 5760.00000000
295 5760.00000000
296 5760.00000000
298 5760.00000000
299 5760.00000000
300 6800.00000000
301 5760.00000000
302 7360.00000000
304 6800.00000000
306 6800.00000000
307 5860.00000000
483 6800.00000000
498 7740.00000000
501 6320.00000000
514 9430.00000000
577 8220.00000000
641 1760.00000000
646 8490.00000000
689 8490.00000000