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 (
    1030, 1437, 1780, 1031, 807, 1487, 806, 
    1032, 1033, 1470, 370, 1573, 1752, 1424, 
    1423, 1744, 934, 1010, 1434, 1818, 153, 
    1290, 1291, 1292, 1917, 1916, 505, 395, 
    1795, 215, 214, 217, 212, 216, 234, 213, 
    1491, 1496, 1497, 1498, 1480, 1570, 
    1571, 1572, 1626, 1784, 1286, 1287
  ) 
  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 (1030,1437,1780,1031,807,1487,806,1032,1033,1470,370,1573,1752,1424,1423,1744,934,1010,1434,1818,153,1290,1291,1292,1917,1916,505,395,1795,215,214,217,212,216,234,213,1491,1496,1497,1498,1480,1570,1571,1572,1626,1784,1286,1287)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
153 0.00000000
212 80000.00000000
213 96000.00000000
214 78544.00000000
215 80000.00000000
216 80000.00000000
217 78544.00000000
234 78544.00000000
370 12000.00000000
395 480000.00000000
505 380000.00000000
806 58000.00000000
807 66000.00000000
934 0.00000000
1010 60000.00000000
1030 58000.00000000
1031 66000.00000000
1032 58000.00000000
1033 58000.00000000
1286 22000.00000000
1287 22000.00000000
1290 56000.00000000
1291 56000.00000000
1292 56000.00000000
1423 66000.00000000
1424 66000.00000000
1434 72000.00000000
1437 58000.00000000
1470 66000.00000000
1480 34000.00000000
1487 58000.00000000
1491 66000.00000000
1496 66000.00000000
1497 66000.00000000
1498 66000.00000000
1570 26000.00000000
1571 26000.00000000
1572 26000.00000000
1573 28000.00000000
1626 0.00000000
1744 0.00000000
1752 34000.00000000
1780 58000.00000000
1784 64000.00000000
1795 405000.00000000
1818 74000.00000000
1916 28000.00000000
1917 32000.00000000