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 (
    1414, 1453, 1452, 1407, 1432, 1451, 1445, 
    1411, 1449, 1406, 1448, 1447, 1413, 
    1439, 1419, 1444, 1443, 1418, 1442, 
    1420, 1441, 1440, 1433, 1438, 1436, 
    1435, 1434, 1415, 1431, 1430, 1429, 
    1428, 1427, 1426, 1425, 1424, 1423, 
    1422, 1421, 1417, 1416, 1412, 1410, 
    153, 1409, 1408, 1405, 297
  ) 
  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.00178

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 (1414,1453,1452,1407,1432,1451,1445,1411,1449,1406,1448,1447,1413,1439,1419,1444,1443,1418,1442,1420,1441,1440,1433,1438,1436,1435,1434,1415,1431,1430,1429,1428,1427,1426,1425,1424,1423,1422,1421,1417,1416,1412,1410,153,1409,1408,1405,297)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
153 0.00000000
297 60000.00000000
1405 27000.00000000
1406 56000.00000000
1407 42000.00000000
1408 70000.00000000
1409 46000.00000000
1410 28000.00000000
1411 22000.00000000
1412 54000.00000000
1413 21000.00000000
1414 21000.00000000
1415 34000.00000000
1416 145000.00000000
1417 122000.00000000
1418 0.00000000
1419 158000.00000000
1420 132000.00000000
1421 50000.00000000
1422 50000.00000000
1423 66000.00000000
1424 66000.00000000
1425 0.00000000
1426 0.00000000
1427 52000.00000000
1428 105000.00000000
1429 105000.00000000
1430 0.00000000
1431 0.00000000
1432 22000.00000000
1433 32000.00000000
1434 72000.00000000
1435 38000.00000000
1436 22500.00000000
1438 35000.00000000
1439 60000.00000000
1440 158000.00000000
1441 158000.00000000
1442 108000.00000000
1443 0.00000000
1444 0.00000000
1445 92000.00000000
1447 0.00000000
1448 0.00000000
1449 44000.00000000
1451 37000.00000000
1452 42000.00000000
1453 42000.00000000