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
  ) 
  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.00247

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 (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)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
54 0.00000000
292 110000.00000000
293 0.00000000
340 0.00000000
343 0.00000000
351 0.00000000
384 0.00000000
447 0.00000000
460 0.00000000
463 0.00000000
467 0.00000000
482 0.00000000
503 0.00000000
521 0.00000000
524 0.00000000
565 0.00000000
615 0.00000000
628 0.00000000
655 0.00000000
672 300000.00000000
691 0.00000000
693 0.00000000
712 0.00000000
721 0.00000000
722 0.00000000
723 0.00000000
750 0.00000000
751 0.00000000
752 0.00000000
754 0.00000000
761 0.00000000
762 0.00000000
824 70000.00000000
825 0.00000000
898 0.00000000
924 0.00000000
1013 98000.00000000
1047 78000.00000000
1139 0.00000000
1140 0.00000000
1141 0.00000000
1142 0.00000000
1143 0.00000000
1144 138000.00000000
1146 0.00000000
1241 115000.00000000
1264 118000.00000000
1408 70000.00000000