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 (
    8145, 8202, 8322, 8306, 8235, 8247, 8250, 
    8238, 8338, 8259, 8149, 8200, 8324, 
    8323, 8325, 8326, 8327, 8328, 8337, 
    8150, 8142, 8151, 8134, 8140, 8122, 
    8127, 8132, 8167, 7885, 8130, 9313, 
    8139, 8143, 8152, 8411, 9291
  ) 
  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.00122

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.21"
    },
    "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": 36,
        "rows_produced_per_join": 7,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (8145,8202,8322,8306,8235,8247,8250,8238,8338,8259,8149,8200,8324,8323,8325,8326,8327,8328,8337,8150,8142,8151,8134,8140,8122,8127,8132,8167,7885,8130,9313,8139,8143,8152,8411,9291))",
        "cost_info": {
          "read_cost": "24.49",
          "eval_cost": "0.72",
          "prefix_cost": "25.21",
          "data_read_per_join": "172"
        },
        "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
7885 11000.00000000
8122 26750.00000000
8127 38250.00000000
8130 32250.00000000
8132 30000.00000000
8134 37750.00000000
8139 24500.00000000
8140 35000.00000000
8142 36000.00000000
8143 103000.00000000
8145 104000.00000000
8149 57250.00000000
8150 57250.00000000
8151 57250.00000000
8152 25750.00000000
8167 29000.00000000
8200 131250.00000000
8202 141750.00000000
8235 31000.00000000
8238 80000.00000000
8247 93000.00000000
8250 93000.00000000
8259 24750.00000000
8306 32250.00000000
8322 172500.00000000
8323 130500.00000000
8324 219000.00000000
8325 855000.00000000
8326 597000.00000000
8327 660000.00000000
8328 78000.00000000
8337 8500.00000000
8338 6750.00000000
8411 51000.00000000
9291 53000.00000000
9313 59000.00000000