SELECT 
  product_prices.product_id, 
  MIN(
    IF(
      product_prices.percentage_discount = 0, 
      product_prices.price, 
      product_prices.price - (
        product_prices.price * product_prices.percentage_discount
      )/ 100
    )
  ) AS price 
FROM 
  product_prices 
WHERE 
  product_prices.product_id IN (
    5978, 2760, 758, 764, 1352, 1390, 2596, 
    2599, 1356, 2595, 1395, 2601, 2600, 
    2598, 1379, 5817, 2768, 5814, 1378, 
    2772, 5843, 5854, 3413, 5956, 5910, 
    3329, 3377, 2543, 5861, 5839, 5830, 
    5801, 5804, 5815, 5849, 5806, 5836, 
    3591, 3374, 5808, 5809, 3409, 6004, 
    5920, 5879, 3403, 5885, 5911
  ) 
  AND product_prices.lower_limit = 1 
  AND product_prices.usergroup_id IN (0, 1) 
GROUP BY 
  product_prices.product_id

Query time 0.00171

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "40.18"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "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": 4,
        "filtered": "10.00",
        "index_condition": "(`goimagine`.`product_prices`.`product_id` in (5978,2760,758,764,1352,1390,2596,2599,1356,2595,1395,2601,2600,2598,1379,5817,2768,5814,1378,2772,5843,5854,3413,5956,5910,3329,3377,2543,5861,5839,5830,5801,5804,5815,5849,5806,5836,3591,3374,5808,5809,3409,6004,5920,5879,3403,5885,5911))",
        "cost_info": {
          "read_cost": "39.70",
          "eval_cost": "0.48",
          "prefix_cost": "40.18",
          "data_read_per_join": "115"
        },
        "used_columns": [
          "id",
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`goimagine`.`product_prices`.`lower_limit` = 1) and (`goimagine`.`product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
758 45.95000000
764 42.95000000
1352 45.95000000
1356 64.95000000
1378 29.95000000
1379 31.95000000
1390 29.95000000
1395 26.95000000
2543 34.95000000
2595 58.95000000
2596 39.95000000
2598 13.95000000
2599 16.95000000
2600 19.95000000
2601 22.95000000
2760 49.95000000
2768 45.95000000
2772 18.95000000
3329 15.95000000
3374 18.95000000
3377 32.95000000
3403 35.95000000
3409 39.95000000
3413 32.95000000
3591 39.95000000
5801 49.95000000
5804 49.95000000
5806 36.95000000
5808 84.95000000
5809 29.95000000
5814 39.95000000
5815 52.95000000
5817 84.95000000
5830 46.95000000
5836 26.95000000
5839 44.95000000
5843 39.95000000
5849 45.95000000
5854 68.95000000
5861 15.95000000
5879 35.95000000
5885 17.95000000
5910 14.95000000
5911 44.95000000
5920 20.95000000
5956 39.95000000
5978 92.95000000
6004 39.95000000