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 (
    1669, 1400, 1673, 403, 358, 1167, 708, 
    1177, 1195, 1208, 1178, 1849, 1846, 
    1848, 1847, 1770, 1769, 1449, 1341, 
    1592, 1474, 1733, 1898, 1899, 1270, 
    1459, 1477, 1891, 1218, 114, 1810, 113, 
    79, 653, 1465, 1494, 1620, 1619, 1624, 
    1681, 1680, 1902
  ) 
  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": 42,
      "filtered": 8.571426392,
      "index_condition": "cscart_product_prices.product_id in (1669,1400,1673,403,358,1167,708,1177,1195,1208,1178,1849,1846,1848,1847,1770,1769,1449,1341,1592,1474,1733,1898,1899,1270,1459,1477,1891,1218,114,1810,113,79,653,1465,1494,1620,1619,1624,1681,1680,1902)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
79 60000.00000000
113 485000.00000000
114 380000.00000000
358 48000.00000000
403 23000.00000000
653 144000.00000000
708 48000.00000000
1167 48000.00000000
1177 48000.00000000
1178 60000.00000000
1195 60000.00000000
1208 58000.00000000
1218 335000.00000000
1270 140000.00000000
1341 119000.00000000
1400 52000.00000000
1449 44000.00000000
1459 0.00000000
1465 38000.00000000
1474 112000.00000000
1477 100000.00000000
1494 120000.00000000
1592 95000.00000000
1619 84000.00000000
1620 115000.00000000
1624 0.00000000
1669 0.00000000
1673 58000.00000000
1680 132000.00000000
1681 0.00000000
1733 112000.00000000
1769 17000.00000000
1770 1000.00000000
1810 285000.00000000
1846 62000.00000000
1847 62000.00000000
1848 63000.00000000
1849 63000.00000000
1891 97000.00000000
1898 0.00000000
1899 0.00000000
1902 148000.00000000