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 (
    107397, 107396, 107395, 107394, 107393, 
    107392, 107391, 107381, 107675, 107493, 
    107492, 107491, 107729, 107728, 107555, 
    107390, 107389, 107388, 107387, 107280
  ) 
  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.00061

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_prices range usergroup,product_id,lower_limit,usergroup_id usergroup 9 40 Using where

Result

product_id price
107280 1.69000000
107381 1.69000000
107387 1.69000000
107388 1.69000000
107389 1.69000000
107390 1.69000000
107391 1.69000000
107392 1.69000000
107393 1.69000000
107394 1.69000000
107395 1.69000000
107396 1.69000000
107397 1.69000000
107491 1.69000000
107492 1.69000000
107493 1.69000000
107555 1.69000000
107675 1.69000000
107728 1.69000000
107729 1.69000000