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 (
    1705, 1704, 1609, 1935, 1936, 1929, 1528, 
    1527, 1526, 1508, 1534, 1533, 1841, 
    1531, 1529, 1530, 1843, 1532, 1515, 
    1904, 1873, 1897, 1896, 1728, 1607, 
    1608, 1606, 1703, 1578, 1396, 1605, 
    1206, 1586, 1934, 1932, 1933, 1656, 
    1542, 1640, 1543, 1655, 1927, 1926, 
    1815, 1808, 1541, 1829, 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, 1436, 1411, 1397, 
    1741, 1330, 1486, 1546, 1651, 1661, 
    255, 1742, 1598, 1565, 1597, 1708, 1694, 
    1732, 1596, 1632, 1618
  ) 
  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.00300

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": 120,
      "filtered": 19.59183121,
      "index_condition": "cscart_product_prices.product_id in (1705,1704,1609,1935,1936,1929,1528,1527,1526,1508,1534,1533,1841,1531,1529,1530,1843,1532,1515,1904,1873,1897,1896,1728,1607,1608,1606,1703,1578,1396,1605,1206,1586,1934,1932,1933,1656,1542,1640,1543,1655,1927,1926,1815,1808,1541,1829,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,1436,1411,1397,1741,1330,1486,1546,1651,1661,255,1742,1598,1565,1597,1708,1694,1732,1596,1632,1618)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
255 66000.00000000
1206 34000.00000000
1330 122000.00000000
1396 160000.00000000
1397 34000.00000000
1411 22000.00000000
1436 22500.00000000
1486 118000.00000000
1508 29000.00000000
1512 28000.00000000
1514 34000.00000000
1515 22500.00000000
1525 28000.00000000
1526 29000.00000000
1527 29000.00000000
1528 29000.00000000
1529 28000.00000000
1530 28000.00000000
1531 28000.00000000
1532 28000.00000000
1533 28000.00000000
1534 28000.00000000
1536 45000.00000000
1537 30000.00000000
1538 48000.00000000
1539 32000.00000000
1540 42000.00000000
1541 0.00000000
1542 0.00000000
1543 26000.00000000
1544 28000.00000000
1546 10500.00000000
1565 22000.00000000
1578 38000.00000000
1581 0.00000000
1582 0.00000000
1586 11000.00000000
1596 128000.00000000
1597 23000.00000000
1598 23000.00000000
1605 26000.00000000
1606 22000.00000000
1607 22000.00000000
1608 22000.00000000
1609 34000.00000000
1618 0.00000000
1632 56000.00000000
1640 26000.00000000
1651 58000.00000000
1655 40000.00000000
1656 40000.00000000
1661 66000.00000000
1675 0.00000000
1678 39000.00000000
1693 35000.00000000
1694 0.00000000
1698 35000.00000000
1699 40000.00000000
1703 76000.00000000
1704 50000.00000000
1705 50000.00000000
1708 70000.00000000
1711 40000.00000000
1728 28000.00000000
1732 30000.00000000
1741 78000.00000000
1742 32000.00000000
1808 25000.00000000
1815 25000.00000000
1828 36000.00000000
1829 36000.00000000
1830 26000.00000000
1833 14000.00000000
1834 14000.00000000
1835 33000.00000000
1838 0.00000000
1841 25000.00000000
1843 28000.00000000
1845 33000.00000000
1869 28000.00000000
1873 63000.00000000
1896 63000.00000000
1897 63000.00000000
1904 26000.00000000
1926 23000.00000000
1927 0.00000000
1928 23000.00000000
1929 28000.00000000
1931 23000.00000000
1932 23000.00000000
1933 23000.00000000
1934 23000.00000000
1935 22500.00000000
1936 23000.00000000
1937 23000.00000000
1938 22500.00000000