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 (
    10808, 10809, 10810, 10811, 10812, 10827, 
    10828, 9839, 12702, 9841, 9848, 9840, 
    9844, 9843, 9846, 9845, 9860, 9859, 
    9858, 9857, 9852, 9861, 9862, 9847, 
    9863, 9856, 9855, 9838, 9849, 9850, 
    9853, 9842, 9851, 9854, 10263, 10264, 
    10265, 10266, 10267, 10268, 10269, 
    10270, 10271, 10272, 10273, 10478, 
    9883, 9901, 10493, 10485, 10476, 10479, 
    10494, 10492, 10468, 9903, 10487, 9899, 
    10473, 10486, 9881, 9896, 9892, 10481
  ) 
  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.00109

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 (10808,10809,10810,10811,10812,10827,10828,9839,12702,9841,9848,9840,9844,9843,9846,9845,9860,9859,9858,9857,9852,9861,9862,9847,9863,9856,9855,9838,9849,9850,9853,9842,9851,9854,10263,10264,10265,10266,10267,10268,10269,10270,10271,10272,10273,10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486,9881,9896,9892,10481)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9838 2150.58000000
9839 2150.58000000
9840 2150.58000000
9841 2150.58000000
9842 2150.58000000
9843 2150.58000000
9844 2150.58000000
9845 2150.58000000
9846 2150.58000000
9847 2150.58000000
9848 2150.58000000
9849 2150.58000000
9850 2150.58000000
9851 2150.58000000
9852 2150.58000000
9853 2150.58000000
9854 2150.58000000
9855 2150.58000000
9856 2150.58000000
9857 2150.58000000
9858 2150.58000000
9859 2150.58000000
9860 2150.58000000
9861 2150.58000000
9862 2150.58000000
9863 2150.58000000
9881 1036.29000000
9883 1036.29000000
9892 1036.29000000
9896 1036.29000000
9899 1036.29000000
9901 1036.29000000
9903 1036.29000000
10263 2150.58000000
10264 2150.58000000
10265 2150.58000000
10266 2150.58000000
10267 2150.58000000
10268 2150.58000000
10269 2150.58000000
10270 2150.58000000
10271 2150.58000000
10272 2150.58000000
10273 2150.58000000
10468 1036.29000000
10473 1036.29000000
10476 1036.29000000
10478 1036.29000000
10479 1036.29000000
10481 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000
10808 1582.30000000
10809 1582.30000000
10810 1582.30000000
10811 1582.30000000
10812 1582.30000000
10827 1582.30000000
10828 1582.30000000
12702 2150.58000000