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 (
    878, 877, 879, 876, 870, 891, 869, 890, 
    871, 892, 277, 785, 784, 1081, 1079, 
    1080, 1078, 1082, 1083, 1077, 248, 919, 
    269, 821, 822, 278, 279, 302, 303, 720, 
    986, 921, 922, 920, 923, 960, 674, 679, 
    948, 678, 526, 677, 680, 527, 959, 676, 
    675, 896
  ) 
  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.00185

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 (878,877,879,876,870,891,869,890,871,892,277,785,784,1081,1079,1080,1078,1082,1083,1077,248,919,269,821,822,278,279,302,303,720,986,921,922,920,923,960,674,679,948,678,526,677,680,527,959,676,675,896)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
248 0.00000000
269 0.00000000
277 38000.00000000
278 0.00000000
279 0.00000000
302 0.00000000
303 0.00000000
526 0.00000000
527 0.00000000
674 0.00000000
675 0.00000000
676 0.00000000
677 0.00000000
678 0.00000000
679 0.00000000
680 0.00000000
720 0.00000000
784 0.00000000
785 34000.00000000
821 26500.00000000
822 26000.00000000
869 0.00000000
870 0.00000000
871 0.00000000
876 0.00000000
877 0.00000000
878 0.00000000
879 0.00000000
890 0.00000000
891 0.00000000
892 0.00000000
896 0.00000000
919 0.00000000
920 0.00000000
921 0.00000000
922 0.00000000
923 0.00000000
948 0.00000000
959 0.00000000
960 0.00000000
986 0.00000000
1077 0.00000000
1078 0.00000000
1079 0.00000000
1080 0.00000000
1081 0.00000000
1082 0.00000000
1083 0.00000000