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 (
    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.00257

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": 64,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (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
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