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 (
    1735, 1602, 1601, 1692, 1696, 1682, 1743, 
    1045, 1046, 1187, 1556, 1555, 1435, 
    1554, 1577, 1194, 1766, 368, 1253, 1553, 
    1371, 1372, 1373, 1374, 1219, 1220, 
    1440, 1441, 1681, 1680, 1800, 1902, 
    1804, 1803, 1221, 1224, 1762, 408, 39, 
    1798, 1797, 1445
  ) 
  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.00163

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 (1735,1602,1601,1692,1696,1682,1743,1045,1046,1187,1556,1555,1435,1554,1577,1194,1766,368,1253,1553,1371,1372,1373,1374,1219,1220,1440,1441,1681,1680,1800,1902,1804,1803,1221,1224,1762,408,39,1798,1797,1445)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
39 80000.00000000
368 0.00000000
408 54000.00000000
1045 72000.00000000
1046 78000.00000000
1187 47000.00000000
1194 42000.00000000
1219 56000.00000000
1220 56000.00000000
1221 195000.00000000
1224 54000.00000000
1253 40000.00000000
1371 34000.00000000
1372 46000.00000000
1373 53000.00000000
1374 27000.00000000
1435 38000.00000000
1440 158000.00000000
1441 158000.00000000
1445 92000.00000000
1553 20000.00000000
1554 20000.00000000
1555 0.00000000
1556 0.00000000
1577 30000.00000000
1601 24500.00000000
1602 24500.00000000
1680 132000.00000000
1681 0.00000000
1682 0.00000000
1692 30000.00000000
1696 52000.00000000
1735 24500.00000000
1743 0.00000000
1762 240000.00000000
1766 115000.00000000
1797 50000.00000000
1798 34000.00000000
1800 150000.00000000
1803 150000.00000000
1804 150000.00000000
1902 148000.00000000