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 (
    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, 
    15943, 
    15951, 
    15952, 
    15956, 
    15958, 
    15959, 
    15944, 
    15960, 
    15961, 
    15962, 
    15966, 
    15957, 
    15965, 
    15945, 
    15938, 
    15968, 
    15946, 
    15947, 
    15949, 
    15950, 
    12520, 
    12497, 
    12528, 
    12512, 
    12511, 
    12527, 
    10580, 
    10579, 
    9936, 
    9931, 
    12521, 
    12504, 
    12502, 
    12505, 
    13099, 
    12514, 
    12513, 
    9759, 
    10496, 
    10560, 
    10561, 
    10581, 
    10102, 
    9940, 
    10578, 
    10143, 
    9947, 
    10567, 
    10568, 
    10565, 
    9933, 
    12499, 
    12500, 
    10200, 
    10201, 
    10566, 
    10545, 
    10227, 
    10552, 
    12506, 
    10693, 
    12508, 
    9953, 
    12522, 
    12507, 
    12498, 
    13101, 
    13100, 
    10699, 
    10695, 
    10195, 
    10576, 
    10696, 
    9760, 
    10583, 
    10701, 
    10582, 
    10139, 
    12503, 
    10575, 
    9932, 
    10075, 
    10135, 
    9952, 
    10074, 
    10573, 
    10577, 
    10651, 
    10642, 
    12529, 
    12515, 
    12525, 
    12532, 
    12523, 
    10198, 
    10557, 
    10202
  ) 
  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.00203

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": 192,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (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,15943,15951,15952,15956,15958,15959,15944,15960,15961,15962,15966,15957,15965,15945,15938,15968,15946,15947,15949,15950,12520,12497,12528,12512,12511,12527,10580,10579,9936,9931,12521,12504,12502,12505,13099,12514,12513,9759,10496,10560,10561,10581,10102,9940,10578,10143,9947,10567,10568,10565,9933,12499,12500,10200,10201,10566,10545,10227,10552,12506,10693,12508,9953,12522,12507,12498,13101,13100,10699,10695,10195,10576,10696,9760,10583,10701,10582,10139,12503,10575,9932,10075,10135,9952,10074,10573,10577,10651,10642,12529,12515,12525,12532,12523,10198,10557,10202)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9759 1955.00000000
9760 1754.00000000
9838 2150.58000000
9842 2150.58000000
9845 2150.58000000
9846 2150.58000000
9847 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
9931 1331.00000000
9932 1563.00000000
9933 1563.00000000
9936 1331.00000000
9940 2142.00000000
9947 2022.00000000
9952 2041.00000000
9953 1960.00000000
10074 2369.00000000
10075 2369.00000000
10102 2142.00000000
10135 2041.00000000
10139 2338.00000000
10143 2205.00000000
10195 1815.00000000
10198 2405.00000000
10200 2234.00000000
10201 2234.00000000
10202 2405.00000000
10227 1997.00000000
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
10496 1955.00000000
10545 2143.00000000
10552 2143.00000000
10557 2658.00000000
10560 2098.00000000
10561 2098.00000000
10565 2691.00000000
10566 2089.00000000
10567 2691.00000000
10568 2691.00000000
10573 2369.00000000
10575 2210.00000000
10576 1815.00000000
10577 2041.00000000
10578 2205.00000000
10579 1791.00000000
10580 1791.00000000
10581 2142.00000000
10582 1754.00000000
10583 1754.00000000
10642 2369.00000000
10651 2041.00000000
10693 2089.00000000
10695 1848.00000000
10696 1840.00000000
10699 1551.00000000
10701 1840.00000000
12497 0.00000000
12498 0.00000000
12499 0.00000000
12500 0.00000000
12502 0.00000000
12503 0.00000000
12504 0.00000000
12505 0.00000000
12506 0.00000000
12507 0.00000000
12508 0.00000000
12511 0.00000000
12512 0.00000000
12513 0.00000000
12514 0.00000000
12515 0.00000000
12520 0.00000000
12521 0.00000000
12522 0.00000000
12523 0.00000000
12525 0.00000000
12527 0.00000000
12528 0.00000000
12529 0.00000000
12532 0.00000000
13099 0.00000000
13100 0.00000000
13101 0.00000000
15938 4889.00000000
15943 6390.00000000
15944 6875.00000000
15945 6890.00000000
15946 6476.00000000
15947 6047.00000000
15949 4982.00000000
15950 4982.00000000
15951 7167.00000000
15952 3852.00000000
15956 6896.00000000
15957 4709.00000000
15958 5578.00000000
15959 6031.00000000
15960 6478.00000000
15961 6499.00000000
15962 6272.00000000
15965 6424.00000000
15966 5975.00000000
15968 4889.00000000