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 (
    3520, 3529, 4962, 1668, 3146, 4958, 4942, 
    3809, 8586, 3518, 3148, 3918, 8372, 
    3215, 3213, 2222, 3400, 5691, 5687, 
    3733, 3552, 2805, 3232, 4915, 6025, 
    3149, 3416, 3157, 3589, 5791, 3845, 
    3150, 2481, 3822, 4976, 3115, 3740, 
    3123, 2228, 3743
  ) 
  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.00109

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 (3520,3529,4962,1668,3146,4958,4942,3809,8586,3518,3148,3918,8372,3215,3213,2222,3400,5691,5687,3733,3552,2805,3232,4915,6025,3149,3416,3157,3589,5791,3845,3150,2481,3822,4976,3115,3740,3123,2228,3743))",
        "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
1668 74000.00000000
2222 39000.00000000
2228 19000.00000000
2481 38000.00000000
2805 54000.00000000
3115 177000.00000000
3123 18000.00000000
3146 5000.00000000
3148 7000.00000000
3149 8000.00000000
3150 10000.00000000
3157 21000.00000000
3213 6000.00000000
3215 12000.00000000
3232 15000.00000000
3400 15000.00000000
3416 40000.00000000
3518 7000.00000000
3520 12000.00000000
3529 15000.00000000
3552 28000.00000000
3589 47000.00000000
3733 7500.00000000
3740 6000.00000000
3743 6000.00000000
3809 12000.00000000
3822 17000.00000000
3845 31000.00000000
3918 89000.00000000
4915 70000.00000000
4942 89000.00000000
4958 215000.00000000
4962 70000.00000000
4976 70000.00000000
5687 120000.00000000
5691 148000.00000000
5791 65000.00000000
6025 134000.00000000
8372 17000.00000000
8586 59000.00000000