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 (
    10073, 10077, 10076, 10086, 10085, 10084, 
    10083, 10082, 10081, 10080, 10079, 
    10584, 10594, 10739, 10740, 10741, 
    10742, 10759, 10760, 10761, 10762, 
    10763, 10764, 10765, 10766, 10778, 
    10779, 10780, 10781, 10782, 10783, 
    10784
  ) 
  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.00044

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": 32,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10073,10077,10076,10086,10085,10084,10083,10082,10081,10080,10079,10584,10594,10739,10740,10741,10742,10759,10760,10761,10762,10763,10764,10765,10766,10778,10779,10780,10781,10782,10783,10784)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10073 1660.30000000
10076 1660.30000000
10077 1660.30000000
10079 1660.30000000
10080 1660.30000000
10081 1660.30000000
10082 1660.30000000
10083 1660.30000000
10084 1660.30000000
10085 1660.30000000
10086 1660.30000000
10584 1660.30000000
10594 1660.30000000
10739 4457.16000000
10740 4457.16000000
10741 4457.16000000
10742 3755.16000000
10759 3264.87000000
10760 3264.87000000
10761 3264.87000000
10762 3264.87000000
10763 3264.87000000
10764 3264.87000000
10765 3264.87000000
10766 3264.87000000
10778 2106.01000000
10779 2106.01000000
10780 2106.01000000
10781 2106.01000000
10782 2106.01000000
10783 2106.01000000
10784 2106.01000000