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, 881, 894, 882, 895, 893, 950, 
    957, 949, 956, 953, 954, 951, 952, 958, 
    1090, 955, 335, 334, 156, 336, 961, 965, 
    963, 964, 962, 966, 219, 270, 1302, 272, 
    273, 274, 973, 223, 222, 160, 194, 164, 
    174, 158, 183, 192, 188, 163, 172, 184, 
    173, 191
  ) 
  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.00292

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 (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,881,894,882,895,893,950,957,949,956,953,954,951,952,958,1090,955,335,334,156,336,961,965,963,964,962,966,219,270,1302,272,273,274,973,223,222,160,194,164,174,158,183,192,188,163,172,184,173,191)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
156 0.00000000
158 0.00000000
160 18000.00000000
163 18000.00000000
164 18000.00000000
172 0.00000000
173 0.00000000
174 0.00000000
183 0.00000000
184 0.00000000
188 0.00000000
191 0.00000000
192 0.00000000
194 0.00000000
219 0.00000000
222 0.00000000
223 0.00000000
248 0.00000000
269 0.00000000
270 0.00000000
272 0.00000000
273 0.00000000
274 0.00000000
277 38000.00000000
278 0.00000000
279 0.00000000
302 0.00000000
303 0.00000000
334 0.00000000
335 0.00000000
336 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
881 0.00000000
882 0.00000000
890 0.00000000
891 0.00000000
892 0.00000000
893 0.00000000
894 0.00000000
895 0.00000000
896 0.00000000
919 0.00000000
920 0.00000000
921 0.00000000
922 0.00000000
923 0.00000000
948 0.00000000
949 0.00000000
950 0.00000000
951 0.00000000
952 0.00000000
953 0.00000000
954 0.00000000
955 0.00000000
956 0.00000000
957 0.00000000
958 0.00000000
959 0.00000000
960 0.00000000
961 0.00000000
962 0.00000000
963 0.00000000
964 0.00000000
965 0.00000000
966 0.00000000
973 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
1090 0.00000000
1302 0.00000000