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 (
    1609, 1611, 1595, 1594, 1606, 1605, 1604, 
    1598, 1597, 1596, 1613, 1614, 1602, 
    1607, 1610, 1601, 1599, 1608, 1612, 
    1603, 1600
  ) 
  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.00025

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14.71"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 21,
        "rows_produced_per_join": 4,
        "filtered": "19.99",
        "index_condition": "(`cscartdevel`.`cscart_product_prices`.`product_id` in (1609,1611,1595,1594,1606,1605,1604,1598,1597,1596,1613,1614,1602,1607,1610,1601,1599,1608,1612,1603,1600))",
        "cost_info": {
          "read_cost": "14.29",
          "eval_cost": "0.42",
          "prefix_cost": "14.71",
          "data_read_per_join": "100"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`cscartdevel`.`cscart_product_prices`.`lower_limit` = 1) and (`cscartdevel`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1594 100.00000000
1595 104.00000000
1596 300.00000000
1597 61.00000000
1598 57.00000000
1599 99.00000000
1600 99.00000000
1601 99.00000000
1602 99.00000000
1603 99.00000000
1604 110.00000000
1605 112.00000000
1606 137.00000000
1607 200.00000000
1608 187.00000000
1609 147.00000000
1610 115.00000000
1611 147.00000000
1612 147.00000000
1613 70.00000000
1614 67.00000000