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 (
    431615, 431616, 431617, 431618, 431619, 
    431620, 431621, 431622, 431623, 431624, 
    431625, 431626, 431627, 431628, 431629, 
    431630, 431631, 431632, 431633, 431634, 
    431635, 431636, 431637, 431638, 431639, 
    431640, 431641, 431642, 431643, 431644, 
    431645, 431646, 431647, 431648, 431649, 
    431650, 431651, 431652, 431653, 431654, 
    431655, 431656, 431657, 431658
  ) 
  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.00172

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "123.34"
    },
    "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": 88,
        "rows_produced_per_join": 88,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "105.74",
          "eval_cost": "17.60",
          "prefix_cost": "123.34",
          "data_read_per_join": "5K"
        },
        "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 (431615,431616,431617,431618,431619,431620,431621,431622,431623,431624,431625,431626,431627,431628,431629,431630,431631,431632,431633,431634,431635,431636,431637,431638,431639,431640,431641,431642,431643,431644,431645,431646,431647,431648,431649,431650,431651,431652,431653,431654,431655,431656,431657,431658)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
431615 9.90000000
431616 9.90000000
431617 9.90000000
431618 9.90000000
431619 9.90000000
431620 9.90000000
431621 9.90000000
431622 9.90000000
431623 9.90000000
431624 9.90000000
431625 9.90000000
431626 9.90000000
431627 9.90000000
431628 9.90000000
431629 9.90000000
431630 9.90000000
431631 9.90000000
431632 9.90000000
431633 9.90000000
431634 9.90000000
431635 9.90000000
431636 9.90000000
431637 9.90000000
431638 9.90000000
431639 9.90000000
431640 9.90000000
431641 9.90000000
431642 9.90000000
431643 9.90000000
431644 9.90000000
431645 9.90000000
431646 9.90000000
431647 9.90000000
431648 9.90000000
431649 9.90000000
431650 9.90000000
431651 9.90000000
431652 9.90000000
431653 9.90000000
431654 9.90000000
431655 9.90000000
431656 9.90000000
431657 9.90000000
431658 9.90000000