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 (
    10102, 
    10643, 
    10178, 
    10177, 
    10606, 
    10644, 
    10101, 
    10175, 
    10100, 
    15962, 
    15961, 
    15960, 
    15959, 
    10094, 
    10093, 
    9813, 
    10097, 
    9760, 
    9759, 
    9934, 
    9933, 
    9932, 
    9931, 
    9936, 
    9947, 
    9953, 
    9952, 
    10074, 
    10096, 
    10075, 
    10602, 
    10087, 
    10130, 
    10603, 
    10135, 
    10134, 
    10139, 
    10138, 
    10137, 
    10141, 
    10143, 
    10142, 
    12628, 
    12627, 
    12629, 
    10095, 
    10195, 
    10194, 
    10193, 
    10190, 
    10496, 
    10198, 
    10202, 
    10201, 
    10200, 
    10227, 
    10226, 
    10651, 
    9945, 
    10642, 
    10601, 
    9727, 
    9728, 
    9729, 
    9730, 
    9731, 
    9732, 
    9733, 
    10181, 
    9734, 
    9735, 
    9724, 
    10655, 
    10654, 
    10605, 
    10604, 
    9873, 
    10653, 
    10646, 
    10645, 
    10647, 
    10648, 
    10649, 
    10726, 
    10650, 
    9789, 
    10547, 
    9796, 
    9874, 
    9875, 
    9879, 
    9887, 
    9941, 
    9942, 
    9943, 
    9944, 
    10744, 
    10745, 
    10746, 
    10739, 
    10740, 
    10741, 
    10742, 
    10734, 
    10735, 
    10736, 
    10737, 
    10738, 
    10688, 
    10727, 
    10728, 
    10729, 
    10730, 
    10731, 
    10732, 
    10733, 
    10713, 
    10714, 
    10715, 
    10716, 
    10717, 
    10718, 
    10719, 
    10720, 
    10721, 
    10722, 
    10723, 
    10704
  ) 
  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.00088

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 (10102,10643,10178,10177,10606,10644,10101,10175,10100,15962,15961,15960,15959,10094,10093,9813,10097,9760,9759,9934,9933,9932,9931,9936,9947,9953,9952,10074,10096,10075,10602,10087,10130,10603,10135,10134,10139,10138,10137,10141,10143,10142,12628,12627,12629,10095,10195,10194,10193,10190,10496,10198,10202,10201,10200,10227,10226,10651,9945,10642,10601,9727,9728,9729,9730,9731,9732,9733,10181,9734,9735,9724,10655,10654,10605,10604,9873,10653,10646,10645,10647,10648,10649,10726,10650,9789,10547,9796,9874,9875,9879,9887,9941,9942,9943,9944,10744,10745,10746,10739,10740,10741,10742,10734,10735,10736,10737,10738,10688,10727,10728,10729,10730,10731,10732,10733,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10704)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9724 1482.01000000
9727 1482.01000000
9728 1482.01000000
9729 1482.01000000
9730 1482.01000000
9731 1482.01000000
9732 1482.01000000
9733 1482.01000000
9734 1482.01000000
9735 1482.01000000
9759 1955.00000000
9760 1754.00000000
9789 1381.72000000
9796 1704.87000000
9813 1849.73000000
9873 2997.44000000
9874 1704.87000000
9875 1704.87000000
9879 1704.87000000
9887 1704.87000000
9931 1331.00000000
9932 1563.00000000
9933 1563.00000000
9934 2039.00000000
9936 1331.00000000
9941 1704.87000000
9942 1704.87000000
9943 1704.87000000
9944 1704.87000000
9945 1482.01000000
9947 2022.00000000
9952 2041.00000000
9953 1960.00000000
10074 2369.00000000
10075 2369.00000000
10087 1635.00000000
10093 1849.73000000
10094 1849.73000000
10095 1849.73000000
10096 1849.73000000
10097 1849.73000000
10100 1635.00000000
10101 2405.00000000
10102 2142.00000000
10130 2121.00000000
10134 2121.00000000
10135 2041.00000000
10137 1924.00000000
10138 1955.00000000
10139 2338.00000000
10141 2153.00000000
10142 2121.00000000
10143 2205.00000000
10175 1849.73000000
10177 1849.73000000
10178 1849.73000000
10181 1482.01000000
10190 2257.00000000
10193 2325.00000000
10194 2257.00000000
10195 1815.00000000
10198 2405.00000000
10200 2234.00000000
10201 2234.00000000
10202 2405.00000000
10226 1524.00000000
10227 1997.00000000
10496 1955.00000000
10547 1381.72000000
10601 1482.01000000
10602 1885.00000000
10603 2517.00000000
10604 2997.44000000
10605 2997.44000000
10606 1849.73000000
10642 2369.00000000
10643 1849.73000000
10644 1849.73000000
10645 1381.72000000
10646 1381.72000000
10647 1381.72000000
10648 1381.72000000
10649 1381.72000000
10650 1381.72000000
10651 2041.00000000
10653 1381.72000000
10654 2997.44000000
10655 2997.44000000
10688 3242.59000000
10704 3432.02000000
10713 1537.72000000
10714 1537.72000000
10715 1537.72000000
10716 1537.72000000
10717 1537.72000000
10718 1537.72000000
10719 1537.72000000
10720 1537.72000000
10721 1537.72000000
10722 1537.72000000
10723 1537.72000000
10726 1381.72000000
10727 3242.59000000
10728 3242.59000000
10729 3242.59000000
10730 3242.59000000
10731 3242.59000000
10732 3242.59000000
10733 3242.59000000
10734 2294.40000000
10735 2294.40000000
10736 2294.40000000
10737 2294.40000000
10738 2294.40000000
10739 4457.16000000
10740 4457.16000000
10741 4457.16000000
10742 3755.16000000
10744 4824.88000000
10745 4824.88000000
10746 4824.88000000
12627 21943.06000000
12628 21943.06000000
12629 21943.06000000
15959 6031.00000000
15960 6478.00000000
15961 6499.00000000
15962 6272.00000000