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 (
    10038, 10039, 10040, 10756, 10757, 10758, 
    10397, 10398, 10399, 10400, 10401, 
    10402, 12700, 10596, 10597, 10598, 
    10599, 10600, 10521, 10520, 10519, 
    10518, 10517, 10516, 10515, 10514, 
    10513, 10512, 10511, 10510, 10509, 
    10508
  ) 
  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.00135

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 (10038,10039,10040,10756,10757,10758,10397,10398,10399,10400,10401,10402,12700,10596,10597,10598,10599,10600,10521,10520,10519,10518,10517,10516,10515,10514,10513,10512,10511,10510,10509,10508)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10038 1716.01000000
10039 1716.01000000
10040 1716.01000000
10397 1326.01000000
10398 1326.01000000
10399 1326.01000000
10400 1326.01000000
10401 1326.01000000
10402 1326.01000000
10508 4033.73000000
10509 4189.73000000
10510 4189.73000000
10511 4189.73000000
10512 4189.73000000
10513 4189.73000000
10514 4189.73000000
10515 4189.73000000
10516 4189.73000000
10517 4189.73000000
10518 3365.16000000
10519 3365.16000000
10520 3365.16000000
10521 3365.16000000
10596 3365.16000000
10597 3365.16000000
10598 3365.16000000
10599 3365.16000000
10600 3365.16000000
10756 7644.03000000
10757 7644.03000000
10758 7644.03000000
12700 1326.01000000