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 (
    10068, 
    10065, 
    10061, 
    10050, 
    10056, 
    10044, 
    10069, 
    10066, 
    12671, 
    12672, 
    15779, 
    12673, 
    12674, 
    12675, 
    12676, 
    12677, 
    12678, 
    12679, 
    10024, 
    10025, 
    10026, 
    10027, 
    10028, 
    10029, 
    10030, 
    10031, 
    10032, 
    10033, 
    10034, 
    10035, 
    10036, 
    10037, 
    10038, 
    10039, 
    10040, 
    10756, 
    10757, 
    10758, 
    10397, 
    10398, 
    10399, 
    10400, 
    10401, 
    10402, 
    12700, 
    10596, 
    10597, 
    10598, 
    10599, 
    10600, 
    10521, 
    10520, 
    10519, 
    10518, 
    10517, 
    10516, 
    10515, 
    10514, 
    10513, 
    10512, 
    10511, 
    10510, 
    10509, 
    10508, 
    10507, 
    10506, 
    10505, 
    10504, 
    10503, 
    10502, 
    10501, 
    10500, 
    10499, 
    10498, 
    10497, 
    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
  ) 
  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.00682

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 (10068,10065,10061,10050,10056,10044,10069,10066,12671,12672,15779,12673,12674,12675,12676,12677,12678,12679,10024,10025,10026,10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,10756,10757,10758,10397,10398,10399,10400,10401,10402,12700,10596,10597,10598,10599,10600,10521,10520,10519,10518,10517,10516,10515,10514,10513,10512,10511,10510,10509,10508,10507,10506,10505,10504,10503,10502,10501,10500,10499,10498,10497,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)",
      "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
9883 1036.29000000
9899 1036.29000000
9901 1036.29000000
9903 1036.29000000
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
10044 1929.60000000
10050 1929.60000000
10056 1929.60000000
10061 1929.60000000
10065 1929.60000000
10066 1929.60000000
10068 1929.60000000
10069 1929.60000000
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
10397 1326.01000000
10398 1326.01000000
10399 1326.01000000
10400 1326.01000000
10401 1326.01000000
10402 1326.01000000
10468 1036.29000000
10473 1036.29000000
10476 1036.29000000
10478 1036.29000000
10479 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000
10497 3599.16000000
10498 3599.16000000
10499 3599.16000000
10500 3599.16000000
10501 3599.16000000
10502 4033.73000000
10503 4033.73000000
10504 4033.73000000
10505 4033.73000000
10506 4033.73000000
10507 4033.73000000
10508 4033.73000000
10509 4189.73000000
10510 4189.73000000
10511 4189.73000000
10512 4189.73000000
10513 4189.73000000
10514 4189.73000000
10515 4189.73000000
10516 4189.73000000
10517 4189.73000000
10518 3365.16000000
10519 3365.16000000
10520 3365.16000000
10521 3365.16000000
10596 3365.16000000
10597 3365.16000000
10598 3365.16000000
10599 3365.16000000
10600 3365.16000000
10756 7644.03000000
10757 7644.03000000
10758 7644.03000000
12671 921.36000000
12672 921.36000000
12673 921.36000000
12674 921.36000000
12675 921.36000000
12676 921.36000000
12677 921.36000000
12678 921.36000000
12679 921.36000000
12700 1326.01000000
12702 2150.58000000
15779 921.36000000