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 (
    38980, 38979, 38978, 38977, 38950, 38949, 
    39126, 38968, 38962, 38961, 38960, 
    38959, 38958, 38957, 38956, 38955, 
    38954, 38953, 38952, 38951, 38930, 
    39545, 39544, 39543, 39542, 39541, 
    39540, 39539, 39538, 39537, 39536, 
    39535, 39534, 39533, 39532, 39531, 
    39530, 39529, 39528, 39527, 39526, 
    39525, 39524, 39523, 39522, 39521, 
    39492, 39491, 39490, 39489, 39488, 
    39487, 39486, 39485, 39484, 39483, 
    39482, 39481, 39480, 39479, 39478, 
    39477, 39476, 39475, 39474, 39473, 
    39357, 39356, 39355, 39354, 39353, 
    39352, 39351, 39350, 39349, 39348, 
    39347, 39346, 39345, 39344
  ) 
  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.00152

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 160 Using where

Result

product_id price
38930 1.69000000
38949 1.69000000
38950 1.69000000
38951 1.69000000
38952 1.69000000
38953 1.69000000
38954 1.69000000
38955 1.69000000
38956 1.69000000
38957 1.69000000
38958 1.69000000
38959 1.69000000
38960 1.69000000
38961 1.69000000
38962 1.69000000
38968 1.69000000
38977 1.69000000
38978 1.69000000
38979 1.69000000
38980 1.69000000
39126 1.69000000
39344 1.69000000
39345 1.69000000
39346 1.69000000
39347 1.69000000
39348 1.69000000
39349 1.69000000
39350 1.69000000
39351 1.69000000
39352 1.69000000
39353 1.69000000
39354 1.69000000
39355 1.69000000
39356 1.69000000
39357 1.69000000
39473 1.69000000
39474 1.69000000
39475 1.69000000
39476 1.69000000
39477 1.69000000
39478 1.69000000
39479 1.69000000
39480 1.69000000
39481 1.69000000
39482 1.69000000
39483 1.69000000
39484 1.69000000
39485 1.69000000
39486 1.69000000
39487 1.69000000
39488 1.69000000
39489 1.69000000
39490 1.69000000
39491 1.69000000
39492 1.69000000
39521 1.69000000
39522 1.69000000
39523 1.69000000
39524 1.69000000
39525 1.69000000
39526 1.69000000
39527 1.69000000
39528 1.69000000
39529 1.69000000
39530 1.69000000
39531 1.69000000
39532 1.69000000
39533 1.69000000
39534 1.69000000
39535 1.69000000
39536 1.69000000
39537 1.69000000
39538 1.69000000
39539 1.69000000
39540 1.69000000
39541 1.69000000
39542 1.69000000
39543 1.69000000
39544 1.69000000
39545 1.69000000