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 (
    9975, 9976, 9977, 9978, 9979, 9876, 9877, 
    9878, 9880, 9881, 9882, 9883, 9885, 
    9886, 9888, 9889, 9890, 9891, 9892, 
    9893, 9894, 9895, 9896, 9897, 9899, 
    9900, 9901, 9903, 9838, 9839, 9840, 
    9841, 9842, 9843, 9844, 9845, 9846, 
    9847, 9848, 9849, 9850, 9851, 9852, 
    9853, 9854, 9855, 9856, 9857, 9858, 
    9859, 9860, 9861, 9862, 9863, 9814, 
    9815, 9816, 9817, 9818, 9820, 9821, 
    9822, 9823, 9824
  ) 
  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.00493

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": 64,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (9975,9976,9977,9978,9979,9876,9877,9878,9880,9881,9882,9883,9885,9886,9888,9889,9890,9891,9892,9893,9894,9895,9896,9897,9899,9900,9901,9903,9838,9839,9840,9841,9842,9843,9844,9845,9846,9847,9848,9849,9850,9851,9852,9853,9854,9855,9856,9857,9858,9859,9860,9861,9862,9863,9814,9815,9816,9817,9818,9820,9821,9822,9823,9824)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9814 1136.58000000
9815 1136.58000000
9816 1136.58000000
9817 1136.58000000
9818 1136.58000000
9820 1136.58000000
9821 1136.58000000
9822 1136.58000000
9823 1136.58000000
9824 1136.58000000
9838 2150.58000000
9839 2150.58000000
9840 2150.58000000
9841 2150.58000000
9842 2150.58000000
9843 2150.58000000
9844 2150.58000000
9845 2150.58000000
9846 2150.58000000
9847 2150.58000000
9848 2150.58000000
9849 2150.58000000
9850 2150.58000000
9851 2150.58000000
9852 2150.58000000
9853 2150.58000000
9854 2150.58000000
9855 2150.58000000
9856 2150.58000000
9857 2150.58000000
9858 2150.58000000
9859 2150.58000000
9860 2150.58000000
9861 2150.58000000
9862 2150.58000000
9863 2150.58000000
9876 1036.29000000
9877 1036.29000000
9878 1036.29000000
9880 1036.29000000
9881 1036.29000000
9882 1036.29000000
9883 1036.29000000
9885 1036.29000000
9886 1036.29000000
9888 1036.29000000
9889 1036.29000000
9890 1036.29000000
9891 1036.29000000
9892 1036.29000000
9893 1036.29000000
9894 1036.29000000
9895 1036.29000000
9896 1036.29000000
9897 1036.29000000
9899 1036.29000000
9900 1036.29000000
9901 1036.29000000
9903 1036.29000000
9975 582.40000000
9976 582.40000000
9977 582.40000000
9978 582.40000000
9979 582.40000000