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 (
    8761, 9563, 9564, 9565, 9566, 9567, 9568, 
    9569, 9570, 9571, 9572, 9573, 9574, 
    9575, 9576, 9577, 9578, 9579, 9580, 
    9581, 9582, 9583, 9584, 9585, 9586, 
    9587, 9588, 9589, 9590, 9591, 9592, 
    9593, 9594, 9595, 9596, 9597, 9598, 
    9599, 9600, 9601, 9602, 9603, 9604, 
    9605, 9606, 9607, 9608, 9609
  ) 
  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.00091

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": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (8761,9563,9564,9565,9566,9567,9568,9569,9570,9571,9572,9573,9574,9575,9576,9577,9578,9579,9580,9581,9582,9583,9584,9585,9586,9587,9588,9589,9590,9591,9592,9593,9594,9595,9596,9597,9598,9599,9600,9601,9602,9603,9604,9605,9606,9607,9608,9609))",
        "cost_info": {
          "read_cost": "32.65",
          "eval_cost": "0.96",
          "prefix_cost": "33.61",
          "data_read_per_join": "230"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
8761 600.00000000
9563 0.00000000
9564 0.00000000
9565 0.00000000
9566 0.00000000
9567 0.00000000
9568 0.00000000
9569 0.00000000
9570 0.00000000
9571 0.00000000
9572 0.00000000
9573 0.00000000
9574 0.00000000
9575 0.00000000
9576 0.00000000
9577 0.00000000
9578 0.00000000
9579 0.00000000
9580 0.00000000
9581 0.00000000
9582 0.00000000
9583 0.00000000
9584 0.00000000
9585 0.00000000
9586 0.00000000
9587 0.00000000
9588 0.00000000
9589 0.00000000
9590 0.00000000
9591 0.00000000
9592 0.00000000
9593 0.00000000
9594 0.00000000
9595 0.00000000
9596 0.00000000
9597 0.00000000
9598 0.00000000
9599 0.00000000
9600 0.00000000
9601 0.00000000
9602 0.00000000
9603 0.00000000
9604 0.00000000
9605 0.00000000
9606 0.00000000
9607 0.00000000
9608 0.00000000
9609 0.00000000