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 (
    12995, 
    12996, 
    12997, 
    12998, 
    10713, 
    10714, 
    10715, 
    10716, 
    10717, 
    10718, 
    10719, 
    10720, 
    10721, 
    10722, 
    10723, 
    10305, 
    10306, 
    10307, 
    10308, 
    10309, 
    10310, 
    10311, 
    10312, 
    12999, 
    13000, 
    13001, 
    13002, 
    13003, 
    13004, 
    13005, 
    13006, 
    13007, 
    13008, 
    13009, 
    13010, 
    13011, 
    13012, 
    9874, 
    9875, 
    9887, 
    9879, 
    9796, 
    9942, 
    9943, 
    9941, 
    9944, 
    10813, 
    10814, 
    10815, 
    10816, 
    10817, 
    10818, 
    10819, 
    10820, 
    10821, 
    10822, 
    10823, 
    10824, 
    10825, 
    10826, 
    10704, 
    10705, 
    10706, 
    10707, 
    10708, 
    10709, 
    10710, 
    10711, 
    10712, 
    10301, 
    10302, 
    10303, 
    10304, 
    9945, 
    10601, 
    9727, 
    9728, 
    9729, 
    9730, 
    9731, 
    9732, 
    9733, 
    10181, 
    9734, 
    9735, 
    9724, 
    10370, 
    10372, 
    10374, 
    10376, 
    10377, 
    10378, 
    10379, 
    10380, 
    10381, 
    10371, 
    10373, 
    10375, 
    10387, 
    10395, 
    10390, 
    10394, 
    10393, 
    10392, 
    10386, 
    10389, 
    10388, 
    10637, 
    10638, 
    10639, 
    10640, 
    10641, 
    13013, 
    13014, 
    13015, 
    13016, 
    13017, 
    13018, 
    13019, 
    13020, 
    13021, 
    13022, 
    13023, 
    13024, 
    13025, 
    13026, 
    13027, 
    13028
  ) 
  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.00175

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 (12995,12996,12997,12998,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10305,10306,10307,10308,10309,10310,10311,10312,12999,13000,13001,13002,13003,13004,13005,13006,13007,13008,13009,13010,13011,13012,9874,9875,9887,9879,9796,9942,9943,9941,9944,10813,10814,10815,10816,10817,10818,10819,10820,10821,10822,10823,10824,10825,10826,10704,10705,10706,10707,10708,10709,10710,10711,10712,10301,10302,10303,10304,9945,10601,9727,9728,9729,9730,9731,9732,9733,10181,9734,9735,9724,10370,10372,10374,10376,10377,10378,10379,10380,10381,10371,10373,10375,10387,10395,10390,10394,10393,10392,10386,10389,10388,10637,10638,10639,10640,10641,13013,13014,13015,13016,13017,13018,13019,13020,13021,13022,13023,13024,13025,13026,13027,13028)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9724 1482.01000000
9727 1482.01000000
9728 1482.01000000
9729 1482.01000000
9730 1482.01000000
9731 1482.01000000
9732 1482.01000000
9733 1482.01000000
9734 1482.01000000
9735 1482.01000000
9796 1704.87000000
9874 1704.87000000
9875 1704.87000000
9879 1704.87000000
9887 1704.87000000
9941 1704.87000000
9942 1704.87000000
9943 1704.87000000
9944 1704.87000000
9945 1482.01000000
10181 1482.01000000
10301 4579.74000000
10302 4579.74000000
10303 4579.74000000
10304 4579.74000000
10305 3225.60000000
10306 3225.60000000
10307 3225.60000000
10308 3225.60000000
10309 3225.60000000
10310 3225.60000000
10311 3225.60000000
10312 3225.60000000
10370 2763.44000000
10371 2763.44000000
10372 2763.44000000
10373 2763.44000000
10374 2763.44000000
10375 2763.44000000
10376 2763.44000000
10377 2763.44000000
10378 2763.44000000
10379 2763.44000000
10380 2763.44000000
10381 2763.44000000
10386 2540.59000000
10387 2540.59000000
10388 2540.59000000
10389 2540.59000000
10390 2540.59000000
10392 2540.59000000
10393 2540.59000000
10394 2540.59000000
10395 2540.59000000
10601 1482.01000000
10637 2540.59000000
10638 2540.59000000
10639 2540.59000000
10640 2540.59000000
10641 2540.59000000
10704 3432.02000000
10705 3432.02000000
10706 3432.02000000
10707 3432.02000000
10708 3432.02000000
10709 3432.02000000
10710 3432.02000000
10711 3432.02000000
10712 3432.02000000
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
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
12995 3186.87000000
12996 3186.87000000
12997 3186.87000000
12998 3186.87000000
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
13013 1927.73000000
13014 1927.73000000
13015 1927.73000000
13016 1927.73000000
13017 1927.73000000
13018 1927.73000000
13019 1927.73000000
13020 1927.73000000
13021 1927.73000000
13022 1927.73000000
13023 1927.73000000
13024 1927.73000000
13025 1927.73000000
13026 1927.73000000
13027 1927.73000000
13028 1927.73000000