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, 
    10785, 
    10786, 
    10787, 
    10788, 
    10789, 
    10790, 
    10791, 
    10767, 
    10768, 
    10769, 
    10770, 
    10771, 
    10772, 
    10773, 
    10774, 
    10775, 
    10776, 
    10777, 
    12944, 
    12985, 
    12986, 
    12987, 
    12988, 
    12989, 
    12990, 
    12991, 
    12992, 
    12993, 
    12994, 
    10063, 
    10052, 
    10072, 
    10046, 
    10059, 
    10064, 
    10057, 
    10048, 
    10062, 
    10070, 
    10058, 
    10045, 
    10054, 
    10047, 
    10071, 
    10067, 
    10051, 
    10060, 
    10055, 
    10043, 
    10068, 
    10065, 
    10061, 
    10050, 
    10056, 
    10044, 
    10069, 
    10066, 
    10024, 
    10025, 
    10026, 
    10027, 
    10028, 
    10029, 
    10030
  ) 
  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.00152

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": 128,
      "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,10785,10786,10787,10788,10789,10790,10791,10767,10768,10769,10770,10771,10772,10773,10774,10775,10776,10777,12944,12985,12986,12987,12988,12989,12990,12991,12992,12993,12994,10063,10052,10072,10046,10059,10064,10057,10048,10062,10070,10058,10045,10054,10047,10071,10067,10051,10060,10055,10043,10068,10065,10061,10050,10056,10044,10069,10066,10024,10025,10026,10027,10028,10029,10030)",
      "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
10024 1716.01000000
10025 1716.01000000
10026 1716.01000000
10027 1716.01000000
10028 1716.01000000
10029 1716.01000000
10030 1716.01000000
10043 1929.60000000
10044 1929.60000000
10045 1672.00000000
10046 1929.60000000
10047 1929.60000000
10048 1929.60000000
10050 1929.60000000
10051 1929.60000000
10052 1929.60000000
10054 1929.60000000
10055 1929.60000000
10056 1929.60000000
10057 1929.60000000
10058 1929.60000000
10059 1929.60000000
10060 1929.60000000
10061 1929.60000000
10062 1929.60000000
10063 1929.60000000
10064 1929.60000000
10065 1929.60000000
10066 1929.60000000
10067 1929.60000000
10068 1929.60000000
10069 1929.60000000
10070 1929.60000000
10071 1929.60000000
10072 1929.60000000
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
10767 5972.60000000
10768 5972.60000000
10769 5972.60000000
10770 5972.60000000
10771 5972.60000000
10772 5972.60000000
10773 5972.60000000
10774 5972.60000000
10775 5972.60000000
10776 5972.60000000
10777 5972.60000000
10778 2106.01000000
10779 2106.01000000
10780 2106.01000000
10781 2106.01000000
10782 2106.01000000
10783 2106.01000000
10784 2106.01000000
10785 2106.01000000
10786 2106.01000000
10787 2106.01000000
10788 2106.01000000
10789 2106.01000000
10790 2106.01000000
10791 2106.01000000
12944 1560.00000000
12985 921.36000000
12986 921.36000000
12987 921.36000000
12988 921.36000000
12989 921.36000000
12990 921.36000000
12991 921.36000000
12992 921.36000000
12993 921.36000000
12994 921.36000000