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 (
    10095, 
    9904, 
    10178, 
    10644, 
    10093, 
    10096, 
    10167, 
    10525, 
    10526, 
    10527, 
    10528, 
    10529, 
    10530, 
    10531, 
    10532, 
    10533, 
    10534, 
    10535, 
    10536, 
    10537, 
    10538, 
    10539, 
    10540, 
    10541, 
    10542, 
    10524, 
    10543, 
    10544, 
    10323, 
    10324, 
    10325, 
    10326, 
    10327, 
    10328, 
    10329, 
    10330, 
    10331, 
    10332, 
    10333, 
    10334, 
    10335, 
    10336, 
    10337, 
    10338, 
    10339, 
    10340, 
    10341, 
    10342, 
    10792, 
    10793, 
    10794, 
    10795, 
    10796, 
    10797, 
    10798, 
    10799, 
    10800, 
    10801, 
    10802, 
    10803, 
    10804, 
    10805, 
    10806, 
    10807, 
    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.00100

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": 128,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10095,9904,10178,10644,10093,10096,10167,10525,10526,10527,10528,10529,10530,10531,10532,10533,10534,10535,10536,10537,10538,10539,10540,10541,10542,10524,10543,10544,10323,10324,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10792,10793,10794,10795,10796,10797,10798,10799,10800,10801,10802,10803,10804,10805,10806,10807,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
9904 1849.73000000
10093 1849.73000000
10095 1849.73000000
10096 1849.73000000
10167 1849.73000000
10178 1849.73000000
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
10323 518.70000000
10324 518.70000000
10325 518.70000000
10326 518.70000000
10327 518.70000000
10328 518.70000000
10329 518.70000000
10330 518.70000000
10331 518.70000000
10332 518.70000000
10333 518.70000000
10334 518.70000000
10335 518.70000000
10336 518.70000000
10337 518.70000000
10338 518.70000000
10339 518.70000000
10340 518.70000000
10341 518.70000000
10342 518.70000000
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
10524 1426.30000000
10525 1426.30000000
10526 1426.30000000
10527 1426.30000000
10528 1426.30000000
10529 1426.30000000
10530 1426.30000000
10531 1426.30000000
10532 1426.30000000
10533 1426.30000000
10534 1426.30000000
10535 1426.30000000
10536 1426.30000000
10537 1426.30000000
10538 1426.30000000
10539 1426.30000000
10540 1426.30000000
10541 1426.30000000
10542 1426.30000000
10543 1426.30000000
10544 1426.30000000
10644 1849.73000000
10792 1582.30000000
10793 1582.30000000
10794 1582.30000000
10795 1582.30000000
10796 1582.30000000
10797 1582.30000000
10798 1582.30000000
10799 1582.30000000
10800 1582.30000000
10801 1582.30000000
10802 1582.30000000
10803 1582.30000000
10804 1582.30000000
10805 1582.30000000
10806 1582.30000000
10807 1582.30000000
10808 1582.30000000
10809 1582.30000000
10810 1582.30000000
10811 1582.30000000
10812 1582.30000000
10827 1582.30000000
10828 1582.30000000
12702 2150.58000000