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 (
    712, 693, 691, 1013, 762, 1241, 1264, 
    723, 751, 503, 628, 467, 754, 752, 655, 
    761, 1142, 1141, 1146, 721, 722, 615, 
    524, 1140, 1143, 1144, 293, 924, 750, 
    898, 565, 447, 460, 482, 1408, 1047, 
    463, 824, 521, 54, 292, 351, 343, 384, 
    340, 1139, 825, 672, 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.00305

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": 96,
      "filtered": 19.59183121,
      "index_condition": "cscart_product_prices.product_id in (712,693,691,1013,762,1241,1264,723,751,503,628,467,754,752,655,761,1142,1141,1146,721,722,615,524,1140,1143,1144,293,924,750,898,565,447,460,482,1408,1047,463,824,521,54,292,351,343,384,340,1139,825,672,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
54 0.00000000
292 110000.00000000
293 0.00000000
316 0.00000000
317 0.00000000
340 0.00000000
343 0.00000000
351 0.00000000
361 0.00000000
379 0.00000000
380 0.00000000
384 0.00000000
415 175000.00000000
430 0.00000000
431 130000.00000000
436 0.00000000
447 0.00000000
449 0.00000000
450 0.00000000
451 0.00000000
460 0.00000000
463 0.00000000
467 0.00000000
482 0.00000000
503 0.00000000
509 0.00000000
512 0.00000000
521 0.00000000
524 0.00000000
529 0.00000000
532 0.00000000
533 95000.00000000
565 0.00000000
586 0.00000000
594 0.00000000
596 0.00000000
614 0.00000000
615 0.00000000
620 0.00000000
623 0.00000000
628 0.00000000
655 0.00000000
672 300000.00000000
691 0.00000000
693 0.00000000
707 0.00000000
712 0.00000000
721 0.00000000
722 0.00000000
723 0.00000000
740 0.00000000
741 0.00000000
743 0.00000000
745 0.00000000
746 0.00000000
747 0.00000000
750 0.00000000
751 0.00000000
752 0.00000000
754 0.00000000
761 0.00000000
762 0.00000000
812 0.00000000
824 70000.00000000
825 0.00000000
834 0.00000000
889 0.00000000
897 0.00000000
898 0.00000000
903 0.00000000
924 0.00000000
939 0.00000000
984 0.00000000
1013 98000.00000000
1047 78000.00000000
1050 0.00000000
1052 0.00000000
1053 0.00000000
1074 0.00000000
1075 0.00000000
1089 0.00000000
1107 0.00000000
1139 0.00000000
1140 0.00000000
1141 0.00000000
1142 0.00000000
1143 0.00000000
1144 138000.00000000
1146 0.00000000
1240 500000.00000000
1241 115000.00000000
1244 247000.00000000
1264 118000.00000000
1278 72000.00000000
1408 70000.00000000