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 (
    1371, 1493, 340, 293, 379, 1159, 311, 
    406, 304, 1058, 1188, 1293, 1116, 1368, 
    1020, 1136, 2500, 2513, 1135, 1252, 
    286, 118, 932, 1138, 1144, 1160, 1200, 
    354, 989, 2503, 1247, 1145
  ) 
  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.00099

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": 4.352192879,
      "index_condition": "cscart_product_prices.product_id in (1371,1493,340,293,379,1159,311,406,304,1058,1188,1293,1116,1368,1020,1136,2500,2513,1135,1252,286,118,932,1138,1144,1160,1200,354,989,2503,1247,1145)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
118 5.3500000000000000
286 0.4400000000000000
293 2000.0000000000000000
304 100.0000000000000000
311 24.6100000000000000
340 0.1100000000000000
354 0.1100000000000000
379 0.1100000000000000
406 0.1100000000000000
932 0.0400000000000000
989 0.3100000000000000
1020 0.3100000000000000
1058 0.6600000000000000
1116 0.6600000000000000
1135 0.4400000000000000
1136 0.4400000000000000
1138 0.4400000000000000
1144 0.4400000000000000
1145 0.4400000000000000
1159 0.7100000000000000
1160 0.7100000000000000
1188 0.6100000000000000
1200 0.8800000000000000
1247 24.6100000000000000
1252 50.0000000000000000
1293 49.0000000000000000
1368 0.5900000000000000
1371 0.5900000000000000
1493 0.9900000000000000
2500 32.0900000000000000
2503 24.6100000000000000
2513 0.4400000000000000