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 (
    13029, 13030, 10106, 10105, 10104, 10103, 
    10625, 10626, 10627, 10628, 10629, 
    10630, 10631, 10632, 10633, 10634, 
    10635, 10636, 10403, 10404, 10405, 
    15781, 10406, 10407, 10408, 10409, 
    10410, 10411, 10412, 12853, 12854, 
    12855, 10414, 10415, 10416, 12856, 
    10417, 10418, 10419, 10420, 10421, 
    10422, 10423, 10424, 10384, 10383, 
    10382, 10753, 10754, 10755, 10296, 
    10297, 10298, 10299, 10300, 10446, 
    10756, 10757, 10758, 13031, 13032, 
    13033, 13034, 13035
  ) 
  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.00150

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 (13029,13030,10106,10105,10104,10103,10625,10626,10627,10628,10629,10630,10631,10632,10633,10634,10635,10636,10403,10404,10405,15781,10406,10407,10408,10409,10410,10411,10412,12853,12854,12855,10414,10415,10416,12856,10417,10418,10419,10420,10421,10422,10423,10424,10384,10383,10382,10753,10754,10755,10296,10297,10298,10299,10300,10446,10756,10757,10758,13031,13032,13033,13034,13035)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10103 4702.31000000
10104 4702.31000000
10105 4702.31000000
10106 4702.31000000
10296 3409.73000000
10297 3409.73000000
10298 3409.73000000
10299 3409.73000000
10300 3409.73000000
10382 4944.00000000
10383 4944.00000000
10384 4944.00000000
10403 1317.94000000
10404 1317.94000000
10405 1317.94000000
10406 1317.94000000
10407 1317.94000000
10408 1317.94000000
10409 1317.94000000
10410 1317.94000000
10411 1317.94000000
10412 1317.94000000
10414 1317.94000000
10415 1317.94000000
10416 1317.94000000
10417 1317.94000000
10418 1317.94000000
10419 1317.94000000
10420 1317.94000000
10421 1317.94000000
10422 1317.94000000
10423 1317.94000000
10424 1317.94000000
10446 3409.73000000
10625 1582.30000000
10626 1582.30000000
10627 1582.30000000
10628 1582.30000000
10629 1582.30000000
10630 1582.30000000
10631 1582.30000000
10632 1582.30000000
10633 1582.30000000
10634 1582.30000000
10635 1582.30000000
10636 1582.30000000
10753 7644.03000000
10754 7644.03000000
10755 7644.03000000
10756 7644.03000000
10757 7644.03000000
10758 7644.03000000
12853 1317.94000000
12854 1317.94000000
12855 1317.94000000
12856 1317.94000000
13029 1927.73000000
13030 1927.73000000
13031 2106.01000000
13032 2106.01000000
13033 2106.01000000
13034 2106.01000000
13035 2106.01000000
15781 1317.94000000