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 (
    532, 623, 509, 897, 361, 512, 746, 740, 
    984, 707, 317, 745, 529, 834, 380, 1278, 
    436, 586, 596, 1075, 450, 743, 741, 449, 
    747, 1053, 415, 45, 1074, 1240, 431, 
    1244, 1050, 1052, 1089, 939, 889, 1107, 
    620, 379, 903, 316, 594, 533, 812, 430, 
    451, 614
  ) 
  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.00128

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 (532,623,509,897,361,512,746,740,984,707,317,745,529,834,380,1278,436,586,596,1075,450,743,741,449,747,1053,415,45,1074,1240,431,1244,1050,1052,1089,939,889,1107,620,379,903,316,594,533,812,430,451,614)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
45 122000.00000000
316 0.00000000
317 0.00000000
361 0.00000000
379 0.00000000
380 0.00000000
415 175000.00000000
430 0.00000000
431 130000.00000000
436 0.00000000
449 0.00000000
450 0.00000000
451 0.00000000
509 0.00000000
512 0.00000000
529 0.00000000
532 0.00000000
533 95000.00000000
586 0.00000000
594 0.00000000
596 0.00000000
614 0.00000000
620 0.00000000
623 0.00000000
707 0.00000000
740 0.00000000
741 0.00000000
743 0.00000000
745 0.00000000
746 0.00000000
747 0.00000000
812 0.00000000
834 0.00000000
889 0.00000000
897 0.00000000
903 0.00000000
939 0.00000000
984 0.00000000
1050 0.00000000
1052 0.00000000
1053 0.00000000
1074 0.00000000
1075 0.00000000
1089 0.00000000
1107 0.00000000
1240 500000.00000000
1244 247000.00000000
1278 72000.00000000