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 (
    107, 73, 102, 1154, 1155, 307, 1157, 1156, 
    227, 487, 1153, 324, 229, 228, 1152, 
    1151, 1149, 1067, 1150, 1063, 1064, 
    905, 490, 1333, 1334, 1332, 567, 570, 
    568, 907, 1000, 999, 995, 1001, 996, 
    992, 998, 997, 993, 991, 909, 908, 1070, 
    469, 225, 226, 1095, 569
  ) 
  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.00184

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 (107,73,102,1154,1155,307,1157,1156,227,487,1153,324,229,228,1152,1151,1149,1067,1150,1063,1064,905,490,1333,1334,1332,567,570,568,907,1000,999,995,1001,996,992,998,997,993,991,909,908,1070,469,225,226,1095,569)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
73 0.00000000
102 0.00000000
107 10000.00000000
225 50000.00000000
226 50000.00000000
227 0.00000000
228 130000.00000000
229 130000.00000000
307 0.00000000
324 130000.00000000
469 0.00000000
487 0.00000000
490 0.00000000
567 0.00000000
568 0.00000000
569 0.00000000
570 0.00000000
905 0.00000000
907 0.00000000
908 0.00000000
909 0.00000000
991 0.00000000
992 0.00000000
993 0.00000000
995 0.00000000
996 0.00000000
997 0.00000000
998 0.00000000
999 0.00000000
1000 0.00000000
1001 0.00000000
1063 0.00000000
1064 0.00000000
1067 0.00000000
1070 0.00000000
1095 0.00000000
1149 0.00000000
1150 0.00000000
1151 0.00000000
1152 0.00000000
1153 0.00000000
1154 0.00000000
1155 0.00000000
1156 0.00000000
1157 0.00000000
1332 138000.00000000
1333 138000.00000000
1334 138000.00000000