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 (
    339511, 339515, 339527, 339513, 339519, 
    339510, 339518, 339508, 339713, 339525, 
    339505, 339530, 339514, 339521, 339551, 
    339501, 339499, 339721, 339495, 339494, 
    339503, 339531, 339516, 339637, 339539, 
    339509, 339636, 339538, 339492, 339506, 
    339724, 339635, 339502, 339537, 339491, 
    339540, 339524, 339493, 339536, 339520, 
    339517, 339522, 339526, 339490, 339498, 
    339528, 339497, 339507, 339512, 339523, 
    339504, 339500, 339532, 339535, 339534, 
    339496, 339533, 339529
  ) 
  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.00318

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "162.58"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "usergroup",
        "used_key_parts": [
          "product_id",
          "usergroup_id",
          "lower_limit"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 116,
        "rows_produced_per_join": 116,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "139.39",
          "eval_cost": "23.20",
          "prefix_cost": "162.59",
          "data_read_per_join": "7K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`webmarco`.`cscart_product_prices`.`lower_limit` = 1) and (`webmarco`.`cscart_product_prices`.`product_id` in (339511,339515,339527,339513,339519,339510,339518,339508,339713,339525,339505,339530,339514,339521,339551,339501,339499,339721,339495,339494,339503,339531,339516,339637,339539,339509,339636,339538,339492,339506,339724,339635,339502,339537,339491,339540,339524,339493,339536,339520,339517,339522,339526,339490,339498,339528,339497,339507,339512,339523,339504,339500,339532,339535,339534,339496,339533,339529)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}