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 (
    243, 246, 244, 242, 245, 240, 290, 508, 
    295, 286, 259, 260, 249, 716, 719, 211, 
    253, 239, 93, 262, 325, 221, 809, 254, 
    1237, 271, 1009, 264, 725, 250, 266, 
    235, 873, 872, 875, 874, 666, 300, 669, 
    664, 309, 287, 288, 668, 667, 308, 662, 
    663
  ) 
  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.00170

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 (243,246,244,242,245,240,290,508,295,286,259,260,249,716,719,211,253,239,93,262,325,221,809,254,1237,271,1009,264,725,250,266,235,873,872,875,874,666,300,669,664,309,287,288,668,667,308,662,663)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
93 35000.00000000
211 151200.00000000
221 0.00000000
235 78544.00000000
239 49200.00000000
240 68400.00000000
242 58800.00000000
243 62400.00000000
244 62400.00000000
245 62400.00000000
246 62400.00000000
249 32400.00000000
250 199200.00000000
253 48000.00000000
254 72540.00000000
259 0.00000000
260 0.00000000
262 0.00000000
264 93600.00000000
266 69600.00000000
271 0.00000000
286 58800.00000000
287 30000.00000000
288 26400.00000000
290 57000.00000000
295 50400.00000000
300 37000.00000000
308 0.00000000
309 27600.00000000
325 97200.00000000
508 6000.00000000
662 27600.00000000
663 27600.00000000
664 0.00000000
666 0.00000000
667 0.00000000
668 0.00000000
669 0.00000000
716 162000.00000000
719 163200.00000000
725 66000.00000000
809 71220.00000000
872 0.00000000
873 0.00000000
874 0.00000000
875 0.00000000
1009 86400.00000000
1237 28000.00000000