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 (
    621, 860, 619, 861, 5653, 59, 122, 5662, 
    61, 2992, 5663, 123, 63, 58, 121, 124, 
    62, 785, 784, 786, 782, 787, 783, 1511, 
    3586, 1924, 1773, 3665, 3666, 4625, 
    6421, 7262, 5971, 1727, 5957, 6422, 
    6423, 6424, 7252, 7253, 7250, 6717, 
    6719, 6718, 7251, 4614, 3869, 1772
  ) 
  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.00102

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 (621,860,619,861,5653,59,122,5662,61,2992,5663,123,63,58,121,124,62,785,784,786,782,787,783,1511,3586,1924,1773,3665,3666,4625,6421,7262,5971,1727,5957,6422,6423,6424,7252,7253,7250,6717,6719,6718,7251,4614,3869,1772))",
        "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
58 11.90000000
59 11.90000000
61 11.90000000
62 11.90000000
63 11.90000000
121 11.90000000
122 11.90000000
123 11.90000000
124 11.90000000
619 8.90000000
621 8.90000000
782 2.90000000
783 2.90000000
784 3.90000000
785 3.90000000
786 3.90000000
787 4.90000000
860 8.90000000
861 8.90000000
1511 2.90000000
1727 14.90000000
1772 3.90000000
1773 69.90000000
1924 8.90000000
2992 6.50000000
3586 19.90000000
3665 69.90000000
3666 69.90000000
3869 32.90000000
4614 32.90000000
4625 69.90000000
5653 11.90000000
5662 11.90000000
5663 11.90000000
5957 19.90000000
5971 19.90000000
6421 89.90000000
6422 19.90000000
6423 19.90000000
6424 19.90000000
6717 19.90000000
6718 19.90000000
6719 19.90000000
7250 19.90000000
7251 19.90000000
7252 19.90000000
7253 19.90000000
7262 89.90000000