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 (
    10361, 
    10362, 
    10363, 
    10364, 
    10365, 
    10366, 
    10367, 
    10368, 
    10704, 
    10705, 
    10706, 
    10707, 
    10708, 
    10709, 
    10710, 
    10711, 
    10712, 
    10301, 
    10302, 
    10303, 
    10304, 
    10425, 
    10426, 
    10427, 
    10428, 
    10429, 
    10430, 
    10431, 
    10432, 
    10433, 
    10434, 
    10435, 
    10436, 
    10437, 
    10438, 
    10439, 
    10440, 
    10441, 
    10442, 
    10443, 
    10444, 
    10522, 
    9945, 
    10601, 
    9727, 
    9728, 
    9729, 
    9730, 
    9731, 
    9732, 
    9733, 
    10181, 
    9734, 
    9735, 
    9724, 
    9803, 
    9805, 
    9797, 
    9800, 
    9799, 
    9798, 
    9801, 
    9802, 
    9806, 
    9804, 
    9809, 
    9810, 
    9811, 
    9812, 
    9807, 
    9808, 
    9683, 
    10455, 
    10456, 
    10454, 
    10453, 
    10452, 
    10451, 
    10450, 
    10449, 
    10448, 
    10447, 
    12691, 
    12692, 
    12693, 
    12694, 
    12695, 
    12696, 
    12697, 
    12698, 
    12699, 
    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, 
    10744, 
    10745, 
    10746, 
    9905, 
    10168, 
    10041, 
    10643, 
    10606, 
    10094, 
    10097, 
    12703
  ) 
  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.00087

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 (10361,10362,10363,10364,10365,10366,10367,10368,10704,10705,10706,10707,10708,10709,10710,10711,10712,10301,10302,10303,10304,10425,10426,10427,10428,10429,10430,10431,10432,10433,10434,10435,10436,10437,10438,10439,10440,10441,10442,10443,10444,10522,9945,10601,9727,9728,9729,9730,9731,9732,9733,10181,9734,9735,9724,9803,9805,9797,9800,9799,9798,9801,9802,9806,9804,9809,9810,9811,9812,9807,9808,9683,10455,10456,10454,10453,10452,10451,10450,10449,10448,10447,12691,12692,12693,12694,12695,12696,12697,12698,12699,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,10744,10745,10746,9905,10168,10041,10643,10606,10094,10097,12703)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9683 4000.00000000
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
9797 1292.58000000
9798 1292.58000000
9799 1292.58000000
9800 1292.58000000
9801 1292.58000000
9802 1292.58000000
9803 1292.58000000
9804 1292.58000000
9805 1292.58000000
9806 1292.58000000
9807 1292.58000000
9808 1292.58000000
9809 1292.58000000
9810 1292.58000000
9811 1292.58000000
9812 1292.58000000
9905 1849.73000000
9945 1482.01000000
10041 1849.73000000
10094 1849.73000000
10097 1849.73000000
10168 1849.73000000
10181 1482.01000000
10301 4579.74000000
10302 4579.74000000
10303 4579.74000000
10304 4579.74000000
10361 2121.60000000
10362 2121.60000000
10363 2121.60000000
10364 2121.60000000
10365 2121.60000000
10366 2121.60000000
10367 2121.60000000
10368 2121.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
10425 1671.44000000
10426 1671.44000000
10427 1671.44000000
10428 1671.44000000
10429 1671.44000000
10430 1671.44000000
10431 1671.44000000
10432 1671.44000000
10433 1671.44000000
10434 1671.44000000
10435 1671.44000000
10436 1671.44000000
10437 1671.44000000
10438 1671.44000000
10439 1671.44000000
10440 1671.44000000
10441 1671.44000000
10442 1671.44000000
10443 1671.44000000
10444 1671.44000000
10447 1894.30000000
10448 1894.30000000
10449 1894.30000000
10450 1894.30000000
10451 1894.30000000
10452 1894.30000000
10453 1894.30000000
10454 1894.30000000
10455 1894.30000000
10456 1894.30000000
10522 1671.44000000
10601 1482.01000000
10606 1849.73000000
10637 2540.59000000
10638 2540.59000000
10639 2540.59000000
10640 2540.59000000
10641 2540.59000000
10643 1849.73000000
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
10744 4824.88000000
10745 4824.88000000
10746 4824.88000000
12691 1894.30000000
12692 1894.30000000
12693 1894.30000000
12694 1894.30000000
12695 1894.30000000
12696 1894.30000000
12697 1894.30000000
12698 1894.30000000
12699 1894.30000000
12703 1849.73000000