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 (
    6290, 6085, 6016, 6017, 6084, 6191, 6018, 
    6086, 6081, 6144, 6254, 6228, 6192, 
    6227, 6083, 6230, 6275, 6277, 6291, 
    6258, 6255, 6229, 6256, 6189, 6257, 
    6259, 6145, 6226, 6279, 6278, 6190, 
    6276, 6280, 6260, 6274, 6281, 6282
  ) 
  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.00209

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.91"
    },
    "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": 37,
        "rows_produced_per_join": 7,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (6290,6085,6016,6017,6084,6191,6018,6086,6081,6144,6254,6228,6192,6227,6083,6230,6275,6277,6291,6258,6255,6229,6256,6189,6257,6259,6145,6226,6279,6278,6190,6276,6280,6260,6274,6281,6282))",
        "cost_info": {
          "read_cost": "25.17",
          "eval_cost": "0.74",
          "prefix_cost": "25.91",
          "data_read_per_join": "177"
        },
        "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
6016 141000.00000000
6017 55000.00000000
6018 78000.00000000
6081 128000.00000000
6083 118000.00000000
6084 169000.00000000
6085 141000.00000000
6086 176000.00000000
6144 192000.00000000
6145 251000.00000000
6189 117500.00000000
6190 124500.00000000
6191 22000.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
6277 79500.00000000
6278 124000.00000000
6279 95000.00000000
6280 136000.00000000
6281 374000.00000000
6282 385000.00000000
6290 114000.00000000
6291 399000.00000000