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 (
    10793, 
    10794, 
    10795, 
    10796, 
    10797, 
    10798, 
    10799, 
    10800, 
    10801, 
    10802, 
    10803, 
    10804, 
    10805, 
    10806, 
    10807, 
    10808, 
    10809, 
    10810, 
    10811, 
    10812, 
    10827, 
    10828, 
    9686, 
    10767, 
    10768, 
    10769, 
    10770, 
    10771, 
    10772, 
    10773, 
    10774, 
    10775, 
    10776, 
    10777, 
    9689, 
    10459, 
    10460, 
    10461, 
    10462, 
    10463, 
    10464, 
    10465, 
    10625, 
    10626, 
    10627, 
    10628, 
    10629, 
    10630, 
    10631, 
    10632, 
    10633, 
    10634, 
    10635, 
    10636, 
    12944, 
    10829, 
    10830, 
    10831, 
    10832, 
    10833, 
    10834, 
    10835, 
    10836, 
    10837, 
    10838, 
    10839, 
    12985, 
    12986, 
    12987, 
    12988, 
    12989, 
    12990, 
    12991, 
    12992, 
    12993, 
    12994, 
    10403, 
    10404, 
    10405, 
    15781, 
    10406, 
    10407, 
    10408, 
    10409, 
    10410, 
    10411, 
    10412, 
    12853, 
    12854, 
    12855, 
    10414, 
    10415, 
    10416, 
    12856, 
    10417, 
    10418, 
    10419, 
    10420, 
    10421, 
    10422, 
    10423, 
    10424, 
    10384, 
    10383, 
    10382, 
    10753, 
    10754, 
    10755, 
    10296, 
    10297, 
    10298, 
    10299, 
    10300, 
    10446, 
    10063, 
    10052, 
    10072, 
    10046, 
    10059, 
    10064, 
    10057, 
    10048, 
    10062, 
    10070, 
    10058, 
    10045, 
    10054, 
    10047
  ) 
  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.00077

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 (10793,10794,10795,10796,10797,10798,10799,10800,10801,10802,10803,10804,10805,10806,10807,10808,10809,10810,10811,10812,10827,10828,9686,10767,10768,10769,10770,10771,10772,10773,10774,10775,10776,10777,9689,10459,10460,10461,10462,10463,10464,10465,10625,10626,10627,10628,10629,10630,10631,10632,10633,10634,10635,10636,12944,10829,10830,10831,10832,10833,10834,10835,10836,10837,10838,10839,12985,12986,12987,12988,12989,12990,12991,12992,12993,12994,10403,10404,10405,15781,10406,10407,10408,10409,10410,10411,10412,12853,12854,12855,10414,10415,10416,12856,10417,10418,10419,10420,10421,10422,10423,10424,10384,10383,10382,10753,10754,10755,10296,10297,10298,10299,10300,10446,10063,10052,10072,10046,10059,10064,10057,10048,10062,10070,10058,10045,10054,10047)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9686 4000.00000000
9689 4000.00000000
10045 1672.00000000
10046 1929.60000000
10047 1929.60000000
10048 1929.60000000
10052 1929.60000000
10054 1929.60000000
10057 1929.60000000
10058 1929.60000000
10059 1929.60000000
10062 1929.60000000
10063 1929.60000000
10064 1929.60000000
10070 1929.60000000
10072 1929.60000000
10296 3409.73000000
10297 3409.73000000
10298 3409.73000000
10299 3409.73000000
10300 3409.73000000
10382 4944.00000000
10383 4944.00000000
10384 4944.00000000
10403 1317.94000000
10404 1317.94000000
10405 1317.94000000
10406 1317.94000000
10407 1317.94000000
10408 1317.94000000
10409 1317.94000000
10410 1317.94000000
10411 1317.94000000
10412 1317.94000000
10414 1317.94000000
10415 1317.94000000
10416 1317.94000000
10417 1317.94000000
10418 1317.94000000
10419 1317.94000000
10420 1317.94000000
10421 1317.94000000
10422 1317.94000000
10423 1317.94000000
10424 1317.94000000
10446 3409.73000000
10459 1708.80000000
10460 1708.80000000
10461 1708.80000000
10462 1708.80000000
10463 1708.80000000
10464 1708.80000000
10465 1708.80000000
10625 1582.30000000
10626 1582.30000000
10627 1582.30000000
10628 1582.30000000
10629 1582.30000000
10630 1582.30000000
10631 1582.30000000
10632 1582.30000000
10633 1582.30000000
10634 1582.30000000
10635 1582.30000000
10636 1582.30000000
10753 7644.03000000
10754 7644.03000000
10755 7644.03000000
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
10793 1582.30000000
10794 1582.30000000
10795 1582.30000000
10796 1582.30000000
10797 1582.30000000
10798 1582.30000000
10799 1582.30000000
10800 1582.30000000
10801 1582.30000000
10802 1582.30000000
10803 1582.30000000
10804 1582.30000000
10805 1582.30000000
10806 1582.30000000
10807 1582.30000000
10808 1582.30000000
10809 1582.30000000
10810 1582.30000000
10811 1582.30000000
10812 1582.30000000
10827 1582.30000000
10828 1582.30000000
10829 3053.16000000
10830 3053.16000000
10831 3053.16000000
10832 3053.16000000
10833 3053.16000000
10834 3053.16000000
10835 3053.16000000
10836 3053.16000000
10837 3053.16000000
10838 3053.16000000
10839 3053.16000000
12853 1317.94000000
12854 1317.94000000
12855 1317.94000000
12856 1317.94000000
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
15781 1317.94000000