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 (
    10478, 9883, 9901, 10493, 10485, 10476, 
    10479, 10494, 10492, 10468, 9903, 10487, 
    9899, 10473, 10486, 9881, 9896, 9892, 
    10481, 9876, 10474, 10491, 10489, 10471, 
    9880, 10472, 9885, 10488, 9897, 9895, 
    10467, 9900, 10470, 10466, 10477, 9889, 
    10475, 9890, 10483, 10495, 10490, 10469, 
    9877, 9894, 9891, 10480, 9893, 9878, 
    9886, 9882, 9888, 10482
  ) 
  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.00059

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": 52,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486,9881,9896,9892,10481,9876,10474,10491,10489,10471,9880,10472,9885,10488,9897,9895,10467,9900,10470,10466,10477,9889,10475,9890,10483,10495,10490,10469,9877,9894,9891,10480,9893,9878,9886,9882,9888,10482)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9876 1036.29000000
9877 1036.29000000
9878 1036.29000000
9880 1036.29000000
9881 1036.29000000
9882 1036.29000000
9883 1036.29000000
9885 1036.29000000
9886 1036.29000000
9888 1036.29000000
9889 1036.29000000
9890 1036.29000000
9891 1036.29000000
9892 1036.29000000
9893 1036.29000000
9894 1036.29000000
9895 1036.29000000
9896 1036.29000000
9897 1036.29000000
9899 1036.29000000
9900 1036.29000000
9901 1036.29000000
9903 1036.29000000
10466 1036.29000000
10467 1036.29000000
10468 1036.29000000
10469 1036.29000000
10470 1036.29000000
10471 1036.29000000
10472 1036.29000000
10473 1036.29000000
10474 1036.29000000
10475 1036.29000000
10476 1036.29000000
10477 1036.29000000
10478 1036.29000000
10479 1036.29000000
10480 1036.29000000
10481 1036.29000000
10482 1036.29000000
10483 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10488 1036.29000000
10489 1036.29000000
10490 1036.29000000
10491 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000
10495 1036.29000000