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 (
    10615, 
    10614, 
    10613, 
    10612, 
    10611, 
    10610, 
    10609, 
    10608, 
    10607, 
    10600, 
    10599, 
    10598, 
    10597, 
    10596, 
    15952, 
    15951, 
    15950, 
    15949, 
    15947, 
    15946, 
    15945, 
    15944, 
    10583, 
    10582, 
    10581, 
    10580, 
    10579, 
    10578, 
    10577, 
    10576, 
    10575, 
    10573, 
    10572, 
    10569, 
    10568, 
    10567, 
    10566, 
    10565, 
    10563, 
    10561, 
    10560, 
    10558, 
    10557, 
    10556, 
    10552, 
    10551, 
    10550, 
    15943, 
    10545, 
    10544, 
    10543, 
    10542, 
    10541, 
    10540, 
    10539, 
    10538, 
    10537, 
    10536, 
    10535, 
    10534, 
    10533, 
    10532, 
    10531, 
    10530, 
    10529, 
    10528, 
    10527, 
    10526, 
    10525, 
    10524, 
    10522, 
    10495, 
    10494, 
    10493, 
    10492, 
    10491, 
    10490, 
    10489, 
    10488, 
    10487, 
    10486, 
    10485, 
    10483, 
    10482, 
    10481, 
    10480, 
    10479, 
    10478, 
    10477, 
    10476, 
    10475, 
    10474, 
    10473, 
    10472, 
    10471, 
    10470, 
    10469, 
    10468, 
    10467, 
    10466, 
    12582, 
    12583, 
    12584, 
    10465, 
    10464, 
    10463, 
    10462, 
    10461, 
    10460, 
    10459, 
    12581, 
    12699, 
    12698, 
    12697, 
    12696, 
    12695, 
    12694, 
    12693, 
    12692, 
    12691, 
    10447, 
    10448, 
    10449, 
    10450, 
    10451, 
    10452, 
    10453, 
    10454
  ) 
  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.00430

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 (10615,10614,10613,10612,10611,10610,10609,10608,10607,10600,10599,10598,10597,10596,15952,15951,15950,15949,15947,15946,15945,15944,10583,10582,10581,10580,10579,10578,10577,10576,10575,10573,10572,10569,10568,10567,10566,10565,10563,10561,10560,10558,10557,10556,10552,10551,10550,15943,10545,10544,10543,10542,10541,10540,10539,10538,10537,10536,10535,10534,10533,10532,10531,10530,10529,10528,10527,10526,10525,10524,10522,10495,10494,10493,10492,10491,10490,10489,10488,10487,10486,10485,10483,10482,10481,10480,10479,10478,10477,10476,10475,10474,10473,10472,10471,10470,10469,10468,10467,10466,12582,12583,12584,10465,10464,10463,10462,10461,10460,10459,12581,12699,12698,12697,12696,12695,12694,12693,12692,12691,10447,10448,10449,10450,10451,10452,10453,10454)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10447 1894.30000000
10448 1894.30000000
10449 1894.30000000
10450 1894.30000000
10451 1894.30000000
10452 1894.30000000
10453 1894.30000000
10454 1894.30000000
10459 1708.80000000
10460 1708.80000000
10461 1708.80000000
10462 1708.80000000
10463 1708.80000000
10464 1708.80000000
10465 1708.80000000
10466 1036.29000000
10467 1036.29000000
10468 1036.29000000
10469 1036.29000000
10470 1036.29000000
10471 1036.29000000
10472 1036.29000000
10473 1036.29000000
10474 1036.29000000
10475 1036.29000000
10476 1036.29000000
10477 1036.29000000
10478 1036.29000000
10479 1036.29000000
10480 1036.29000000
10481 1036.29000000
10482 1036.29000000
10483 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10488 1036.29000000
10489 1036.29000000
10490 1036.29000000
10491 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000
10495 1036.29000000
10522 1671.44000000
10524 1426.30000000
10525 1426.30000000
10526 1426.30000000
10527 1426.30000000
10528 1426.30000000
10529 1426.30000000
10530 1426.30000000
10531 1426.30000000
10532 1426.30000000
10533 1426.30000000
10534 1426.30000000
10535 1426.30000000
10536 1426.30000000
10537 1426.30000000
10538 1426.30000000
10539 1426.30000000
10540 1426.30000000
10541 1426.30000000
10542 1426.30000000
10543 1426.30000000
10544 1426.30000000
10545 2143.00000000
10550 1995.00000000
10551 1995.00000000
10552 2143.00000000
10556 2658.00000000
10557 2658.00000000
10558 2658.00000000
10560 2098.00000000
10561 2098.00000000
10563 2039.00000000
10565 2691.00000000
10566 2089.00000000
10567 2691.00000000
10568 2691.00000000
10569 2325.00000000
10572 2325.00000000
10573 2369.00000000
10575 2210.00000000
10576 1815.00000000
10577 2041.00000000
10578 2205.00000000
10579 1791.00000000
10580 1791.00000000
10581 2142.00000000
10582 1754.00000000
10583 1754.00000000
10596 3365.16000000
10597 3365.16000000
10598 3365.16000000
10599 3365.16000000
10600 3365.16000000
10607 1292.58000000
10608 1292.58000000
10609 1292.58000000
10610 1292.58000000
10611 1292.58000000
10612 1292.58000000
10613 1292.58000000
10614 1292.58000000
10615 1292.58000000
12581 70507.12000000
12582 41728.72000000
12583 41728.72000000
12584 41160.00000000
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
15943 6390.00000000
15944 6875.00000000
15945 6890.00000000
15946 6476.00000000
15947 6047.00000000
15949 4982.00000000
15950 4982.00000000
15951 7167.00000000
15952 3852.00000000