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 (
    10071, 10067, 10051, 10060, 10055, 10043, 
    10068, 10065, 10061, 10050, 10056, 
    10044, 10069, 10066, 12671, 12672, 
    15779, 12673, 12674, 12675, 12676, 
    12677, 12678, 12679, 9687, 10024, 10025, 
    10026, 10027, 10028, 10029, 10030, 
    10031, 10032, 10033, 10034, 10035, 
    10036, 10037, 10038, 10039, 10040, 
    10756, 10757, 10758, 10397, 10398, 
    10399, 10400, 10401, 10402, 12700, 
    12546, 12633, 12573, 12548, 12580, 
    12576, 12565, 12567, 12549, 12581, 
    12557, 12558
  ) 
  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.00165

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 (10071,10067,10051,10060,10055,10043,10068,10065,10061,10050,10056,10044,10069,10066,12671,12672,15779,12673,12674,12675,12676,12677,12678,12679,9687,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10756,10757,10758,10397,10398,10399,10400,10401,10402,12700,12546,12633,12573,12548,12580,12576,12565,12567,12549,12581,12557,12558)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9687 4000.00000000
10024 1716.01000000
10025 1716.01000000
10026 1716.01000000
10027 1716.01000000
10028 1716.01000000
10029 1716.01000000
10030 1716.01000000
10031 1716.01000000
10032 1716.01000000
10033 1716.01000000
10034 1716.01000000
10035 1716.01000000
10036 1716.01000000
10037 1716.01000000
10038 1716.01000000
10039 1716.01000000
10040 1716.01000000
10043 1929.60000000
10044 1929.60000000
10050 1929.60000000
10051 1929.60000000
10055 1929.60000000
10056 1929.60000000
10060 1929.60000000
10061 1929.60000000
10065 1929.60000000
10066 1929.60000000
10067 1929.60000000
10068 1929.60000000
10069 1929.60000000
10071 1929.60000000
10397 1326.01000000
10398 1326.01000000
10399 1326.01000000
10400 1326.01000000
10401 1326.01000000
10402 1326.01000000
10756 7644.03000000
10757 7644.03000000
10758 7644.03000000
12546 67629.15000000
12548 52280.84000000
12549 43647.16000000
12557 52280.84000000
12558 82018.56000000
12565 67629.15000000
12567 10552.12000000
12573 68108.92000000
12576 41728.72000000
12580 11031.68000000
12581 70507.12000000
12633 67629.15000000
12671 921.36000000
12672 921.36000000
12673 921.36000000
12674 921.36000000
12675 921.36000000
12676 921.36000000
12677 921.36000000
12678 921.36000000
12679 921.36000000
12700 1326.01000000
15779 921.36000000