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 (
    6114, 5647, 6146, 6187, 5651, 6086, 6299, 
    6307, 5508, 6081, 6144, 6254, 6228, 
    6192, 6227, 6083, 6230, 6275, 6258, 
    6255, 6229, 6256, 6189, 6257, 6259, 
    6145, 5644, 6226, 6297, 6190, 6276, 
    6303, 6260, 6305, 6274, 6301, 10074, 
    10088, 10157, 6822
  ) 
  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.00113

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "28.01"
    },
    "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": 40,
        "rows_produced_per_join": 7,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (6114,5647,6146,6187,5651,6086,6299,6307,5508,6081,6144,6254,6228,6192,6227,6083,6230,6275,6258,6255,6229,6256,6189,6257,6259,6145,5644,6226,6297,6190,6276,6303,6260,6305,6274,6301,10074,10088,10157,6822))",
        "cost_info": {
          "read_cost": "27.21",
          "eval_cost": "0.80",
          "prefix_cost": "28.01",
          "data_read_per_join": "191"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
5508 170000.00000000
5644 103500.00000000
5647 108000.00000000
5651 85000.00000000
6081 128000.00000000
6083 118000.00000000
6086 176000.00000000
6114 125000.00000000
6144 192000.00000000
6145 251000.00000000
6146 52000.00000000
6187 60000.00000000
6189 117500.00000000
6190 124500.00000000
6192 62000.00000000
6226 22000.00000000
6227 39000.00000000
6228 65000.00000000
6229 62000.00000000
6230 96000.00000000
6254 95000.00000000
6255 116500.00000000
6256 108500.00000000
6257 97500.00000000
6258 83000.00000000
6259 108000.00000000
6260 57000.00000000
6274 65000.00000000
6275 54000.00000000
6276 54000.00000000
6297 85000.00000000
6299 85000.00000000
6301 97000.00000000
6303 66000.00000000
6305 53500.00000000
6307 156000.00000000
6822 85000.00000000
10074 70000.00000000
10088 60000.00000000
10157 52000.00000000