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 (
    982, 981, 983, 828, 421, 647, 315, 624, 
    937, 1131, 671, 670, 42, 43, 1051, 936, 
    935, 599, 900, 44, 404, 835, 836, 1263, 
    322, 311, 312, 744, 742, 837, 537, 839, 
    621, 1048, 1049, 1014, 575, 1034, 1243, 
    1073, 1295, 497, 704, 700, 702, 705, 
    709, 698
  ) 
  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.00173

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (982,981,983,828,421,647,315,624,937,1131,671,670,42,43,1051,936,935,599,900,44,404,835,836,1263,322,311,312,744,742,837,537,839,621,1048,1049,1014,575,1034,1243,1073,1295,497,704,700,702,705,709,698)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
42 120000.00000000
43 120000.00000000
44 288000.00000000
311 0.00000000
312 0.00000000
315 0.00000000
322 96000.00000000
404 37000.00000000
421 75000.00000000
497 65000.00000000
537 0.00000000
575 0.00000000
599 0.00000000
621 0.00000000
624 0.00000000
647 0.00000000
670 0.00000000
671 0.00000000
698 0.00000000
700 0.00000000
702 0.00000000
704 0.00000000
705 0.00000000
709 0.00000000
742 0.00000000
744 0.00000000
828 0.00000000
835 105000.00000000
836 105000.00000000
837 0.00000000
839 0.00000000
900 0.00000000
935 0.00000000
936 0.00000000
937 0.00000000
981 0.00000000
982 0.00000000
983 0.00000000
1014 0.00000000
1034 110000.00000000
1048 0.00000000
1049 0.00000000
1051 0.00000000
1073 0.00000000
1131 0.00000000
1243 62000.00000000
1263 30000.00000000
1295 435000.00000000