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 (
    394, 402, 129, 126, 147, 152, 1041, 818, 
    816, 66, 27, 28, 1159, 1160, 1158, 1164, 
    1165, 119, 120, 121, 731, 90, 82, 829, 
    1007, 1006, 1003, 1004, 1005, 108, 109, 
    110, 1318, 92, 80, 55, 140, 134, 350, 
    21, 91, 136, 135, 1228, 933, 1239, 70, 
    123
  ) 
  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.00171

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 (394,402,129,126,147,152,1041,818,816,66,27,28,1159,1160,1158,1164,1165,119,120,121,731,90,82,829,1007,1006,1003,1004,1005,108,109,110,1318,92,80,55,140,134,350,21,91,136,135,1228,933,1239,70,123)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
21 58000.00000000
27 52000.00000000
28 52000.00000000
55 57600.00000000
66 52000.00000000
70 81000.00000000
80 35000.00000000
82 26400.00000000
90 26400.00000000
91 66000.00000000
92 35000.00000000
108 54000.00000000
109 54000.00000000
110 54000.00000000
119 48000.00000000
120 48000.00000000
121 48000.00000000
123 52000.00000000
126 45000.00000000
129 150000.00000000
134 36000.00000000
135 66000.00000000
136 66000.00000000
140 36000.00000000
147 48000.00000000
152 45000.00000000
350 70000.00000000
394 0.00000000
402 0.00000000
731 45600.00000000
816 0.00000000
818 0.00000000
829 66000.00000000
933 69600.00000000
1003 49200.00000000
1004 49200.00000000
1005 0.00000000
1006 0.00000000
1007 44000.00000000
1041 0.00000000
1158 52000.00000000
1159 52000.00000000
1160 52000.00000000
1164 52000.00000000
1165 52000.00000000
1228 63000.00000000
1239 26000.00000000
1318 35000.00000000