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 (
    12944, 10043, 10044, 10045, 10046, 10047, 
    10048, 10050, 10051, 10052, 10054, 
    10055, 10056, 10057, 10058, 10059, 
    10060, 10061, 10062, 10063, 10064, 
    10065, 10066, 10067, 10068, 10069, 
    10070, 10071, 10072, 10024, 10025, 
    10026, 10027, 10028, 10029, 10030, 
    10031, 10032, 10033, 10034, 10035, 
    10036, 10037, 10038, 10039, 10040, 
    9984, 9985, 9986, 9987, 9988, 9989, 
    9990, 9991, 9992, 9993, 9994, 9995, 
    9996, 9997, 9998, 9999, 9972, 9973
  ) 
  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.00655

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 64,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (12944,10043,10044,10045,10046,10047,10048,10050,10051,10052,10054,10055,10056,10057,10058,10059,10060,10061,10062,10063,10064,10065,10066,10067,10068,10069,10070,10071,10072,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,9972,9973)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9972 582.40000000
9973 582.40000000
9984 1241.41000000
9985 1241.41000000
9986 1241.41000000
9987 1241.41000000
9988 1241.41000000
9989 1241.41000000
9990 1241.41000000
9991 1241.41000000
9992 1241.41000000
9993 1241.41000000
9994 1241.41000000
9995 1241.41000000
9996 1241.41000000
9997 1241.41000000
9998 1241.41000000
9999 1241.41000000
10024 1716.01000000
10025 1716.01000000
10026 1716.01000000
10027 1716.01000000
10028 1716.01000000
10029 1716.01000000
10030 1716.01000000
10031 1716.01000000
10032 1716.01000000
10033 1716.01000000
10034 1716.01000000
10035 1716.01000000
10036 1716.01000000
10037 1716.01000000
10038 1716.01000000
10039 1716.01000000
10040 1716.01000000
10043 1929.60000000
10044 1929.60000000
10045 1672.00000000
10046 1929.60000000
10047 1929.60000000
10048 1929.60000000
10050 1929.60000000
10051 1929.60000000
10052 1929.60000000
10054 1929.60000000
10055 1929.60000000
10056 1929.60000000
10057 1929.60000000
10058 1929.60000000
10059 1929.60000000
10060 1929.60000000
10061 1929.60000000
10062 1929.60000000
10063 1929.60000000
10064 1929.60000000
10065 1929.60000000
10066 1929.60000000
10067 1929.60000000
10068 1929.60000000
10069 1929.60000000
10070 1929.60000000
10071 1929.60000000
10072 1929.60000000
12944 1560.00000000