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 (
    614832, 1232975, 588096, 880671, 1088508, 
    697953, 796373, 591739, 612280, 1120145, 
    695183, 600012, 614835, 1302601, 590335, 
    866417, 589124, 696523, 593274, 1232966, 
    1232977, 696950, 621062, 596399, 588307, 
    1434961, 590366, 697334, 590358, 594343, 
    866380, 1233023, 562160, 694679, 595794, 
    589041
  ) 
  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.00320

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "77.50"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "usergroup",
        "used_key_parts": [
          "product_id",
          "usergroup_id",
          "lower_limit"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 72,
        "rows_produced_per_join": 72,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "70.30",
          "eval_cost": "7.20",
          "prefix_cost": "77.50",
          "data_read_per_join": "1K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`maniamall_cscart`.`cscart_product_prices`.`lower_limit` = 1) and (`maniamall_cscart`.`cscart_product_prices`.`product_id` in (614832,1232975,588096,880671,1088508,697953,796373,591739,612280,1120145,695183,600012,614835,1302601,590335,866417,589124,696523,593274,1232966,1232977,696950,621062,596399,588307,1434961,590366,697334,590358,594343,866380,1233023,562160,694679,595794,589041)) and (`maniamall_cscart`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
562160 93.740000
588096 84.380000
588307 105.630000
589041 123.500000
589124 175.500000
590335 7.500000
590358 3.750000
590366 5.630000
591739 92.810000
593274 31.250000
594343 9.990000
595794 71.250000
596399 75.000000
600012 38.740000
612280 12.490000
614832 27.490000
614835 28.740000
621062 34.990000
694679 68.740000
695183 8.750000
696523 41.240000
696950 71.240000
697334 37.490000
697953 14.990000
796373 46.240000
866380 43.740000
866417 34.990000
880671 33.740000
1088508 4.990000
1120145 22.490000
1232966 48.740000
1232975 41.240000
1232977 41.240000
1233023 89.990000
1302601 56.240000
1434961 3.740000