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 (
    10420, 10419, 10418, 10417, 12856, 10416, 
    10415, 10414, 12855, 12854, 12853, 
    10412, 10411, 10410, 10409, 10408, 
    10407, 10406, 15781, 10405, 10404, 
    10403, 10402, 10401, 10400, 10399, 
    10398, 10397, 10395, 10394, 10393, 
    10392
  ) 
  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.00086

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": 32,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10420,10419,10418,10417,12856,10416,10415,10414,12855,12854,12853,10412,10411,10410,10409,10408,10407,10406,15781,10405,10404,10403,10402,10401,10400,10399,10398,10397,10395,10394,10393,10392)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10392 2540.59000000
10393 2540.59000000
10394 2540.59000000
10395 2540.59000000
10397 1326.01000000
10398 1326.01000000
10399 1326.01000000
10400 1326.01000000
10401 1326.01000000
10402 1326.01000000
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
12853 1317.94000000
12854 1317.94000000
12855 1317.94000000
12856 1317.94000000
15781 1317.94000000