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 (
    10744, 10745, 10746, 10688, 10727, 10728, 
    10729, 10730, 10731, 10732, 10733, 
    10313, 10314, 10315, 10316, 10317, 
    10318, 10319, 10320, 10321, 10322, 
    10459, 10460, 10461, 10462, 10463, 
    10464, 10465, 10829, 10830, 10831, 
    10832, 10833, 10834, 10835, 10836, 
    10837, 10838, 10839, 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
  ) 
  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.00491

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": 69,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10744,10745,10746,10688,10727,10728,10729,10730,10731,10732,10733,10313,10314,10315,10316,10317,10318,10319,10320,10321,10322,10459,10460,10461,10462,10463,10464,10465,10829,10830,10831,10832,10833,10834,10835,10836,10837,10838,10839,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)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
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
10459 1708.80000000
10460 1708.80000000
10461 1708.80000000
10462 1708.80000000
10463 1708.80000000
10464 1708.80000000
10465 1708.80000000
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
10688 3242.59000000
10727 3242.59000000
10728 3242.59000000
10729 3242.59000000
10730 3242.59000000
10731 3242.59000000
10732 3242.59000000
10733 3242.59000000
10744 4824.88000000
10745 4824.88000000
10746 4824.88000000
10829 3053.16000000
10830 3053.16000000
10831 3053.16000000
10832 3053.16000000
10833 3053.16000000
10834 3053.16000000
10835 3053.16000000
10836 3053.16000000
10837 3053.16000000
10838 3053.16000000
10839 3053.16000000