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 (
    9824, 
    9825, 
    9826, 
    9827, 
    9828, 
    9829, 
    9830, 
    10241, 
    10242, 
    10243, 
    10244, 
    10245, 
    10246, 
    10247, 
    10248, 
    10249, 
    10250, 
    10251, 
    10252, 
    10253, 
    10254, 
    10255, 
    10256, 
    10257, 
    10258, 
    10259, 
    10260, 
    10261, 
    10713, 
    10714, 
    10715, 
    10716, 
    10717, 
    10718, 
    10719, 
    10720, 
    10721, 
    10722, 
    10723, 
    10739, 
    10740, 
    10741, 
    10742, 
    10305, 
    10306, 
    10307, 
    10308, 
    10309, 
    10310, 
    10311, 
    10312, 
    12999, 
    13000, 
    13001, 
    13002, 
    13003, 
    13004, 
    13005, 
    13006, 
    13007, 
    13008, 
    13009, 
    13010, 
    13011, 
    13012, 
    10759, 
    10760, 
    10761, 
    10762, 
    10763, 
    10764, 
    10765, 
    10766, 
    9874, 
    9875, 
    9887, 
    9879, 
    9796, 
    9942, 
    9943, 
    9941, 
    9944, 
    10813, 
    10814, 
    10815, 
    10816, 
    10817, 
    10818, 
    10819, 
    10820, 
    10821, 
    10822, 
    10823, 
    10824, 
    10825, 
    10826, 
    12969, 
    10144, 
    10145, 
    10146, 
    10147, 
    10148, 
    10149, 
    10150, 
    10151, 
    10152, 
    10293, 
    10294, 
    10295, 
    10724, 
    10725, 
    10343, 
    10345, 
    10346, 
    10347, 
    10348, 
    10349, 
    10350, 
    10351, 
    10352, 
    10353, 
    10354, 
    10355, 
    10356, 
    10357, 
    10358, 
    10359, 
    10360
  ) 
  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.00150

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 (9824,9825,9826,9827,9828,9829,9830,10241,10242,10243,10244,10245,10246,10247,10248,10249,10250,10251,10252,10253,10254,10255,10256,10257,10258,10259,10260,10261,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10739,10740,10741,10742,10305,10306,10307,10308,10309,10310,10311,10312,12999,13000,13001,13002,13003,13004,13005,13006,13007,13008,13009,13010,13011,13012,10759,10760,10761,10762,10763,10764,10765,10766,9874,9875,9887,9879,9796,9942,9943,9941,9944,10813,10814,10815,10816,10817,10818,10819,10820,10821,10822,10823,10824,10825,10826,12969,10144,10145,10146,10147,10148,10149,10150,10151,10152,10293,10294,10295,10724,10725,10343,10345,10346,10347,10348,10349,10350,10351,10352,10353,10354,10355,10356,10357,10358,10359,10360)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9796 1704.87000000
9824 1136.58000000
9825 1136.58000000
9826 1136.58000000
9827 1136.58000000
9828 1136.58000000
9829 1136.58000000
9830 1136.58000000
9874 1704.87000000
9875 1704.87000000
9879 1704.87000000
9887 1704.87000000
9941 1704.87000000
9942 1704.87000000
9943 1704.87000000
9944 1704.87000000
10144 1606.00000000
10145 1606.00000000
10146 1606.00000000
10147 1606.00000000
10148 1606.00000000
10149 1606.00000000
10150 1606.00000000
10151 1606.00000000
10152 1606.00000000
10241 1136.58000000
10242 1136.58000000
10243 1136.58000000
10244 1136.58000000
10245 1136.58000000
10246 1136.58000000
10247 1136.58000000
10248 1136.58000000
10249 1136.58000000
10250 1136.58000000
10251 1136.58000000
10252 1136.58000000
10253 1136.58000000
10254 1136.58000000
10255 1136.58000000
10256 1136.58000000
10257 1136.58000000
10258 1136.58000000
10259 1136.58000000
10260 1136.58000000
10261 1136.58000000
10293 2206.30000000
10294 2206.30000000
10295 2206.30000000
10305 3225.60000000
10306 3225.60000000
10307 3225.60000000
10308 3225.60000000
10309 3225.60000000
10310 3225.60000000
10311 3225.60000000
10312 3225.60000000
10343 2121.60000000
10345 2121.60000000
10346 2121.60000000
10347 2121.60000000
10348 2121.60000000
10349 2121.60000000
10350 2121.60000000
10351 2121.60000000
10352 2121.60000000
10353 2121.60000000
10354 2121.60000000
10355 2121.60000000
10356 2121.60000000
10357 2121.60000000
10358 2121.60000000
10359 2121.60000000
10360 2121.60000000
10713 1537.72000000
10714 1537.72000000
10715 1537.72000000
10716 1537.72000000
10717 1537.72000000
10718 1537.72000000
10719 1537.72000000
10720 1537.72000000
10721 1537.72000000
10722 1537.72000000
10723 1537.72000000
10724 2206.30000000
10725 2206.30000000
10739 4457.16000000
10740 4457.16000000
10741 4457.16000000
10742 3755.16000000
10759 3264.87000000
10760 3264.87000000
10761 3264.87000000
10762 3264.87000000
10763 3264.87000000
10764 3264.87000000
10765 3264.87000000
10766 3264.87000000
10813 1704.87000000
10814 1704.87000000
10815 1704.87000000
10816 1704.87000000
10817 1704.87000000
10818 1704.87000000
10819 1704.87000000
10820 1704.87000000
10821 1704.87000000
10822 1704.87000000
10823 1704.87000000
10824 1704.87000000
10825 1704.87000000
10826 1704.87000000
12969 1606.00000000
12999 2061.44000000
13000 2061.44000000
13001 2061.44000000
13002 2061.44000000
13003 2061.44000000
13004 2061.44000000
13005 2061.44000000
13006 2061.44000000
13007 2061.44000000
13008 2061.44000000
13009 2061.44000000
13010 2061.44000000
13011 2061.44000000
13012 2061.44000000