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 (
    4414, 4412, 4416, 7588, 7589, 7590, 7591, 
    7592, 7240, 7237, 7236, 7238, 7239, 
    5875, 5877, 5874, 5879, 5880, 5873, 
    5876, 5878, 5881, 5872, 3999, 4004, 
    4005, 4003, 4002, 4006, 4001, 4000, 
    4598, 2510, 2507, 2508, 2511, 2509, 
    3753, 489, 493, 494, 488, 492, 416, 497, 
    495, 3182, 3181
  ) 
  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.00045

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 (4414,4412,4416,7588,7589,7590,7591,7592,7240,7237,7236,7238,7239,5875,5877,5874,5879,5880,5873,5876,5878,5881,5872,3999,4004,4005,4003,4002,4006,4001,4000,4598,2510,2507,2508,2511,2509,3753,489,493,494,488,492,416,497,495,3182,3181))",
        "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
416 10.90000000
488 9.90000000
489 10.90000000
492 10.90000000
493 10.90000000
494 10.90000000
495 10.90000000
497 10.90000000
2507 12.90000000
2508 12.90000000
2509 12.90000000
2510 12.90000000
2511 12.90000000
3181 14.90000000
3182 14.90000000
3753 9.90000000
3999 10.90000000
4000 10.90000000
4001 10.90000000
4002 10.90000000
4003 10.90000000
4004 10.90000000
4005 10.90000000
4006 10.90000000
4412 12.90000000
4414 12.90000000
4416 12.90000000
4598 12.90000000
5872 19.90000000
5873 19.90000000
5874 19.90000000
5875 19.90000000
5876 19.90000000
5877 19.90000000
5878 19.90000000
5879 19.90000000
5880 19.90000000
5881 19.90000000
7236 14.90000000
7237 14.90000000
7238 14.90000000
7239 14.90000000
7240 14.90000000
7588 14.90000000
7589 14.90000000
7590 14.90000000
7591 14.90000000
7592 14.90000000