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 (
    10102, 10643, 10178, 10177, 10606, 10644, 
    10101, 10175, 10100, 15962, 15961, 
    15960, 15959, 10094, 10093, 9813, 10097, 
    9760, 9759, 9934, 9933, 9932, 9931, 
    9936, 9947, 9953, 9952, 10074, 10096, 
    10075, 10602, 10087
  ) 
  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.00064

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 (10102,10643,10178,10177,10606,10644,10101,10175,10100,15962,15961,15960,15959,10094,10093,9813,10097,9760,9759,9934,9933,9932,9931,9936,9947,9953,9952,10074,10096,10075,10602,10087)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9759 1955.00000000
9760 1754.00000000
9813 1849.73000000
9931 1331.00000000
9932 1563.00000000
9933 1563.00000000
9934 2039.00000000
9936 1331.00000000
9947 2022.00000000
9952 2041.00000000
9953 1960.00000000
10074 2369.00000000
10075 2369.00000000
10087 1635.00000000
10093 1849.73000000
10094 1849.73000000
10096 1849.73000000
10097 1849.73000000
10100 1635.00000000
10101 2405.00000000
10102 2142.00000000
10175 1849.73000000
10177 1849.73000000
10178 1849.73000000
10602 1885.00000000
10606 1849.73000000
10643 1849.73000000
10644 1849.73000000
15959 6031.00000000
15960 6478.00000000
15961 6499.00000000
15962 6272.00000000