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 (
    9984, 9985, 9986, 9987, 9988, 9989, 9990, 
    9991, 9992, 9993, 9994, 9995, 9996, 
    9997, 9998, 9999, 10747, 10748, 10749, 
    10750, 10751, 10752, 9972, 9973, 9975, 
    9976, 9977, 9978, 9979, 10179, 10180, 
    10523, 10073, 10077, 10076, 10086, 
    10085, 10084, 10083, 10082, 10081, 
    10080, 10079, 10584, 10594, 10739, 
    10740, 10741, 10742, 10759, 10760, 
    10761, 10762, 10763, 10764, 10765, 
    10766, 10778, 10779, 10780, 10781, 
    10782, 10783, 10784
  ) 
  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.00068

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 (9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,10747,10748,10749,10750,10751,10752,9972,9973,9975,9976,9977,9978,9979,10179,10180,10523,10073,10077,10076,10086,10085,10084,10083,10082,10081,10080,10079,10584,10594,10739,10740,10741,10742,10759,10760,10761,10762,10763,10764,10765,10766,10778,10779,10780,10781,10782,10783,10784)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9972 582.40000000
9973 582.40000000
9975 582.40000000
9976 582.40000000
9977 582.40000000
9978 582.40000000
9979 582.40000000
9984 1241.41000000
9985 1241.41000000
9986 1241.41000000
9987 1241.41000000
9988 1241.41000000
9989 1241.41000000
9990 1241.41000000
9991 1241.41000000
9992 1241.41000000
9993 1241.41000000
9994 1241.41000000
9995 1241.41000000
9996 1241.41000000
9997 1241.41000000
9998 1241.41000000
9999 1241.41000000
10073 1660.30000000
10076 1660.30000000
10077 1660.30000000
10079 1660.30000000
10080 1660.30000000
10081 1660.30000000
10082 1660.30000000
10083 1660.30000000
10084 1660.30000000
10085 1660.30000000
10086 1660.30000000
10179 1660.30000000
10180 1660.30000000
10523 1660.30000000
10584 1660.30000000
10594 1660.30000000
10739 4457.16000000
10740 4457.16000000
10741 4457.16000000
10742 3755.16000000
10747 6184.31000000
10748 6184.31000000
10749 6184.31000000
10750 6184.31000000
10751 6184.31000000
10752 6184.31000000
10759 3264.87000000
10760 3264.87000000
10761 3264.87000000
10762 3264.87000000
10763 3264.87000000
10764 3264.87000000
10765 3264.87000000
10766 3264.87000000
10778 2106.01000000
10779 2106.01000000
10780 2106.01000000
10781 2106.01000000
10782 2106.01000000
10783 2106.01000000
10784 2106.01000000