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 (
    1206, 1384, 1389, 1393, 1404, 1403, 1402, 
    1401, 1398, 1400, 1394, 1399, 1397, 
    1396, 1395, 1340, 1337, 1338, 1339, 
    1347, 1390, 1342, 1341, 1016, 1381, 
    25, 1392, 1391, 1166, 1388, 1380, 1330, 
    1387, 1386, 1385, 1383, 1382, 1369, 
    1379, 1378, 1377, 1376, 1375, 1374, 
    1373, 1372, 1371, 1370
  ) 
  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.00176

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1206,1384,1389,1393,1404,1403,1402,1401,1398,1400,1394,1399,1397,1396,1395,1340,1337,1338,1339,1347,1390,1342,1341,1016,1381,25,1392,1391,1166,1388,1380,1330,1387,1386,1385,1383,1382,1369,1379,1378,1377,1376,1375,1374,1373,1372,1371,1370)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
25 0.00000000
1016 0.00000000
1166 0.00000000
1206 34000.00000000
1330 122000.00000000
1337 88000.00000000
1338 88000.00000000
1339 100000.00000000
1340 57000.00000000
1341 119000.00000000
1342 26000.00000000
1347 0.00000000
1369 122000.00000000
1370 92000.00000000
1371 34000.00000000
1372 46000.00000000
1373 53000.00000000
1374 27000.00000000
1375 28000.00000000
1376 25000.00000000
1377 0.00000000
1378 12000.00000000
1379 12000.00000000
1380 33000.00000000
1381 30000.00000000
1382 11000.00000000
1383 11000.00000000
1384 55000.00000000
1385 9000.00000000
1386 11000.00000000
1387 22000.00000000
1388 45000.00000000
1389 12500.00000000
1390 0.00000000
1391 45000.00000000
1392 45000.00000000
1393 38000.00000000
1394 31000.00000000
1395 0.00000000
1396 160000.00000000
1397 34000.00000000
1398 75000.00000000
1399 22000.00000000
1400 52000.00000000
1401 55000.00000000
1402 55000.00000000
1403 55000.00000000
1404 2000.00000000