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 (
    10177, 
    10175, 
    9813, 
    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, 
    13013, 
    13014, 
    13015, 
    13016, 
    13017, 
    13018, 
    13019, 
    13020, 
    13021, 
    13022, 
    13023, 
    13024, 
    13025, 
    13026, 
    13027, 
    13028, 
    13029, 
    13030, 
    10688, 
    10727, 
    10728, 
    10729, 
    10730, 
    10731, 
    10732, 
    10733, 
    10313, 
    10314, 
    10315, 
    10316, 
    10317, 
    10318, 
    10319, 
    10320, 
    10321, 
    10322, 
    9690, 
    10106, 
    10105, 
    10104, 
    10103, 
    10206, 
    10209, 
    10216, 
    10203, 
    10204, 
    10205, 
    10207, 
    10208, 
    10210, 
    10211, 
    10212, 
    10213, 
    10214, 
    10215, 
    10217, 
    10218, 
    10219, 
    10220, 
    10221, 
    10222, 
    10323, 
    10324, 
    10325, 
    10326, 
    10327, 
    10328, 
    10329, 
    10330, 
    10331, 
    10332, 
    10333, 
    10334, 
    10335, 
    10336, 
    10337, 
    10338, 
    10339, 
    10340, 
    10341, 
    10342, 
    10778, 
    10779, 
    10780, 
    10781, 
    10782, 
    10783, 
    10784, 
    10785, 
    10786, 
    10787, 
    10788, 
    10789, 
    10790, 
    10791, 
    9688, 
    10792
  ) 
  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.00223

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 (10177,10175,9813,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,13013,13014,13015,13016,13017,13018,13019,13020,13021,13022,13023,13024,13025,13026,13027,13028,13029,13030,10688,10727,10728,10729,10730,10731,10732,10733,10313,10314,10315,10316,10317,10318,10319,10320,10321,10322,9690,10106,10105,10104,10103,10206,10209,10216,10203,10204,10205,10207,10208,10210,10211,10212,10213,10214,10215,10217,10218,10219,10220,10221,10222,10323,10324,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10778,10779,10780,10781,10782,10783,10784,10785,10786,10787,10788,10789,10790,10791,9688,10792)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9688 4000.00000000
9690 4000.00000000
9813 1849.73000000
9904 1849.73000000
10093 1849.73000000
10095 1849.73000000
10096 1849.73000000
10103 4702.31000000
10104 4702.31000000
10105 4702.31000000
10106 4702.31000000
10167 1849.73000000
10175 1849.73000000
10177 1849.73000000
10178 1849.73000000
10203 1894.30000000
10204 1894.30000000
10205 1894.30000000
10206 1894.30000000
10207 1894.30000000
10208 1894.30000000
10209 1894.30000000
10210 1894.30000000
10211 1894.30000000
10212 1894.30000000
10213 1894.30000000
10214 1894.30000000
10215 1894.30000000
10216 1894.30000000
10217 1894.30000000
10218 1894.30000000
10219 1894.30000000
10220 1894.30000000
10221 1894.30000000
10222 1894.30000000
10313 2930.59000000
10314 2930.59000000
10315 2930.59000000
10316 2930.59000000
10317 2930.59000000
10318 2930.59000000
10319 2930.59000000
10320 2930.59000000
10321 2930.59000000
10322 2930.59000000
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
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
10688 3242.59000000
10727 3242.59000000
10728 3242.59000000
10729 3242.59000000
10730 3242.59000000
10731 3242.59000000
10732 3242.59000000
10733 3242.59000000
10778 2106.01000000
10779 2106.01000000
10780 2106.01000000
10781 2106.01000000
10782 2106.01000000
10783 2106.01000000
10784 2106.01000000
10785 2106.01000000
10786 2106.01000000
10787 2106.01000000
10788 2106.01000000
10789 2106.01000000
10790 2106.01000000
10791 2106.01000000
10792 1582.30000000
13013 1927.73000000
13014 1927.73000000
13015 1927.73000000
13016 1927.73000000
13017 1927.73000000
13018 1927.73000000
13019 1927.73000000
13020 1927.73000000
13021 1927.73000000
13022 1927.73000000
13023 1927.73000000
13024 1927.73000000
13025 1927.73000000
13026 1927.73000000
13027 1927.73000000
13028 1927.73000000
13029 1927.73000000
13030 1927.73000000