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 (
    507, 7925, 3554, 3552, 3553, 3447, 4214, 
    4554, 3446, 4604, 7603, 7604, 7605, 
    7606, 4059, 3865, 4054, 4058, 4052, 
    4056, 4053, 3870, 3872, 3871, 3873, 
    3862, 262, 1785, 866, 3676, 3677, 323, 
    865, 864, 322, 324, 5071, 6426, 6428, 
    6429, 6427, 5615, 5613, 5614, 5611, 
    5612, 5692, 5693
  ) 
  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.00068

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.61"
    },
    "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": 48,
        "rows_produced_per_join": 9,
        "filtered": "19.12",
        "index_condition": "(`dbggbern`.`cscart_product_prices`.`product_id` in (507,7925,3554,3552,3553,3447,4214,4554,3446,4604,7603,7604,7605,7606,4059,3865,4054,4058,4052,4056,4053,3870,3872,3871,3873,3862,262,1785,866,3676,3677,323,865,864,322,324,5071,6426,6428,6429,6427,5615,5613,5614,5611,5612,5692,5693))",
        "cost_info": {
          "read_cost": "32.69",
          "eval_cost": "0.92",
          "prefix_cost": "33.61",
          "data_read_per_join": "220"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`dbggbern`.`cscart_product_prices`.`lower_limit` = 1) and (`dbggbern`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
262 39.90000000
322 29.90000000
323 29.90000000
324 29.90000000
507 34.90000000
864 29.90000000
865 29.90000000
866 29.90000000
1785 169.90000000
3446 44.90000000
3447 44.90000000
3552 14.90000000
3553 14.90000000
3554 14.90000000
3676 23.90000000
3677 23.90000000
3862 44.90000000
3865 39.90000000
3870 44.90000000
3871 44.90000000
3872 44.90000000
3873 44.90000000
4052 39.90000000
4053 39.90000000
4054 39.90000000
4056 39.90000000
4058 39.90000000
4059 39.90000000
4214 44.90000000
4554 44.90000000
4604 44.90000000
5071 44.90000000
5611 34.90000000
5612 34.90000000
5613 34.90000000
5614 34.90000000
5615 34.90000000
5692 34.90000000
5693 34.90000000
6426 44.90000000
6427 44.90000000
6428 44.90000000
6429 44.90000000
7603 39.90000000
7604 39.90000000
7605 39.90000000
7606 39.90000000
7925 3.90000000