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 (
    1828, 1675, 1931, 1544, 1845, 1540, 1512, 
    1525, 1536, 1538, 1937, 1581, 1582, 
    1838, 1938, 1830, 1514, 1835, 1678, 
    1693, 1711, 1699, 1698, 1869, 1834, 
    1833, 1928, 1537, 1539, 1381, 1320, 
    359, 1892, 1898, 1899, 1436, 1411, 1397, 
    1741, 1270, 1376, 1301, 1330, 1215, 
    1439, 1323, 717, 1217
  ) 
  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.00184

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": 56,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1828,1675,1931,1544,1845,1540,1512,1525,1536,1538,1937,1581,1582,1838,1938,1830,1514,1835,1678,1693,1711,1699,1698,1869,1834,1833,1928,1537,1539,1381,1320,359,1892,1898,1899,1436,1411,1397,1741,1270,1376,1301,1330,1215,1439,1323,717,1217)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
359 155000.00000000
717 118000.00000000
1215 0.00000000
1217 122000.00000000
1270 140000.00000000
1301 63000.00000000
1320 22000.00000000
1323 110000.00000000
1330 122000.00000000
1376 25000.00000000
1381 30000.00000000
1397 34000.00000000
1411 22000.00000000
1436 22500.00000000
1439 60000.00000000
1512 28000.00000000
1514 34000.00000000
1525 28000.00000000
1536 45000.00000000
1537 30000.00000000
1538 48000.00000000
1539 32000.00000000
1540 42000.00000000
1544 28000.00000000
1581 0.00000000
1582 0.00000000
1675 0.00000000
1678 39000.00000000
1693 35000.00000000
1698 35000.00000000
1699 40000.00000000
1711 40000.00000000
1741 78000.00000000
1828 36000.00000000
1830 26000.00000000
1833 14000.00000000
1834 14000.00000000
1835 33000.00000000
1838 0.00000000
1845 33000.00000000
1869 28000.00000000
1892 145000.00000000
1898 0.00000000
1899 0.00000000
1928 23000.00000000
1931 23000.00000000
1937 23000.00000000
1938 22500.00000000