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 (
    1097, 1057, 1058, 772, 768, 770, 774, 
    776, 777, 773, 769, 771, 775, 1117, 1116, 
    633, 1118, 1019, 563, 611, 1060, 544, 
    1069, 1068, 631, 1111, 632, 1112, 1062, 
    1101, 906, 1099, 831, 830, 1065, 1056, 
    1059, 543, 510, 1017, 779, 778, 1113, 
    1114, 1115, 1096, 1061, 1100
  ) 
  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.00180

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1097,1057,1058,772,768,770,774,776,777,773,769,771,775,1117,1116,633,1118,1019,563,611,1060,544,1069,1068,631,1111,632,1112,1062,1101,906,1099,831,830,1065,1056,1059,543,510,1017,779,778,1113,1114,1115,1096,1061,1100)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
510 0.00000000
543 0.00000000
544 0.00000000
563 0.00000000
611 0.00000000
631 0.00000000
632 0.00000000
633 0.00000000
768 0.00000000
769 0.00000000
770 0.00000000
771 0.00000000
772 0.00000000
773 0.00000000
774 0.00000000
775 0.00000000
776 0.00000000
777 0.00000000
778 0.00000000
779 0.00000000
830 138000.00000000
831 138000.00000000
906 0.00000000
1017 0.00000000
1019 0.00000000
1056 0.00000000
1057 0.00000000
1058 0.00000000
1059 0.00000000
1060 0.00000000
1061 0.00000000
1062 0.00000000
1065 0.00000000
1068 0.00000000
1069 0.00000000
1096 0.00000000
1097 0.00000000
1099 0.00000000
1100 0.00000000
1101 0.00000000
1111 0.00000000
1112 0.00000000
1113 0.00000000
1114 0.00000000
1115 0.00000000
1116 0.00000000
1117 0.00000000
1118 0.00000000