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 (
    1517, 1518, 1519, 1520, 1521, 1522, 1523, 
    1524, 1525, 1537, 1538, 1539, 1540, 
    1541, 1542, 1543, 1544, 1545, 2217, 
    2218, 2219, 2735, 2863, 2864, 3729, 
    3732, 5220, 5221, 5222, 5223, 5224, 
    5225, 5226, 5227, 5237, 5238, 5239, 
    5240, 5241, 5242, 5243, 5244, 5817, 
    5818, 5819, 6322, 6448, 6449, 7494, 
    7495, 7498, 7499, 7504, 7505, 7526, 
    7527
  ) 
  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": "39.66"
    },
    "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": 57,
        "rows_produced_per_join": 11,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (1517,1518,1519,1520,1521,1522,1523,1524,1525,1537,1538,1539,1540,1541,1542,1543,1544,1545,2217,2218,2219,2735,2863,2864,3729,3732,5220,5221,5222,5223,5224,5225,5226,5227,5237,5238,5239,5240,5241,5242,5243,5244,5817,5818,5819,6322,6448,6449,7494,7495,7498,7499,7504,7505,7526,7527))",
        "cost_info": {
          "read_cost": "38.52",
          "eval_cost": "1.14",
          "prefix_cost": "39.66",
          "data_read_per_join": "273"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1517 0.00000000
1518 0.00000000
1519 0.00000000
1520 0.00000000
1521 0.00000000
1522 0.00000000
1523 0.00000000
1524 0.00000000
1525 0.00000000
1537 0.00000000
1538 0.00000000
1539 0.00000000
1540 0.00000000
1541 0.00000000
1542 0.00000000
1543 0.00000000
1544 0.00000000
1545 0.00000000
2217 0.00000000
2218 0.00000000
2219 0.00000000
2735 0.00000000
2863 0.00000000
2864 0.00000000
3729 0.00000000
3732 0.00000000
5220 0.00000000
5221 0.00000000
5222 0.00000000
5223 0.00000000
5224 0.00000000
5225 0.00000000
5226 0.00000000
5227 0.00000000
5237 0.00000000
5238 0.00000000
5239 0.00000000
5240 0.00000000
5241 0.00000000
5242 0.00000000
5243 0.00000000
5244 0.00000000
5817 0.00000000
5818 0.00000000
5819 0.00000000
6322 0.00000000
6448 0.00000000
6449 0.00000000
7494 0.00000000
7495 0.00000000
7498 0.00000000
7499 0.00000000
7504 0.00000000
7505 0.00000000
7526 0.00000000
7527 0.00000000