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 (
    3112, 3944, 3664, 3126, 2230, 2655, 3741, 
    5683, 3440, 5890, 2226, 5859, 3843, 
    3840, 3130, 3441, 3811, 6054, 2221, 
    5891, 3153, 3924, 3377, 2522, 2719, 
    6041, 5111, 3385, 5948, 8240, 3551, 
    3831, 5927, 3289, 10078, 3382, 3519, 
    3356, 6093, 6194
  ) 
  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.00159

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 (3112,3944,3664,3126,2230,2655,3741,5683,3440,5890,2226,5859,3843,3840,3130,3441,3811,6054,2221,5891,3153,3924,3377,2522,2719,6041,5111,3385,5948,8240,3551,3831,5927,3289,10078,3382,3519,3356,6093,6194))",
        "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
2221 65000.00000000
2226 22000.00000000
2230 14000.00000000
2522 51000.00000000
2655 11000.00000000
2719 18000.00000000
3112 171000.00000000
3126 25000.00000000
3130 28000.00000000
3153 13000.00000000
3289 17000.00000000
3356 31000.00000000
3377 65000.00000000
3382 15000.00000000
3385 10000.00000000
3440 9000.00000000
3441 9000.00000000
3519 11000.00000000
3551 31000.00000000
3664 5000.00000000
3741 6000.00000000
3811 8000.00000000
3831 25000.00000000
3840 28000.00000000
3843 28000.00000000
3924 14000.00000000
3944 60000.00000000
5111 7000.00000000
5683 134000.00000000
5859 19000.00000000
5890 91000.00000000
5891 52000.00000000
5927 229000.00000000
5948 69000.00000000
6041 69000.00000000
6054 69000.00000000
6093 82000.00000000
6194 70000.00000000
8240 24000.00000000
10078 7000.00000000