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 (
    1274, 1406, 1266, 1428, 1263, 1256, 1319, 
    1273, 4526, 1532, 4796, 4562, 8499, 
    8502, 8501, 4798, 1284, 1528, 4767, 
    1517, 1542, 4744, 8498, 8497, 1540, 
    1544, 4571, 4812, 1538, 8503, 8505, 
    8496, 4565, 4570, 4567, 4568, 10136, 
    10137, 4569, 5774
  ) 
  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.00169

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 (1274,1406,1266,1428,1263,1256,1319,1273,4526,1532,4796,4562,8499,8502,8501,4798,1284,1528,4767,1517,1542,4744,8498,8497,1540,1544,4571,4812,1538,8503,8505,8496,4565,4570,4567,4568,10136,10137,4569,5774))",
        "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
1256 130000.00000000
1263 1120000.00000000
1266 1130000.00000000
1273 1760000.00000000
1274 337000.00000000
1284 486000.00000000
1319 510000.00000000
1406 760000.00000000
1428 390000.00000000
1517 1812000.00000000
1528 1630000.00000000
1532 845000.00000000
1538 935000.00000000
1540 123000.00000000
1542 194000.00000000
1544 269000.00000000
4526 1300000.00000000
4562 1480000.00000000
4565 140000.00000000
4567 499000.00000000
4568 815000.00000000
4569 419000.00000000
4570 1475000.00000000
4571 705000.00000000
4744 390000.00000000
4767 310000.00000000
4796 1715000.00000000
4798 2010000.00000000
4812 260000.00000000
5774 755000.00000000
8496 740000.00000000
8497 3375000.00000000
8498 2132000.00000000
8499 3175000.00000000
8501 550000.00000000
8502 2129000.00000000
8503 3525000.00000000
8505 2375000.00000000
10136 2988000.00000000
10137 3979000.00000000