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 (
    728, 96, 141, 95, 149, 148, 150, 133, 132, 
    131, 94, 385, 138, 137, 139, 127, 128, 
    130, 101, 146, 124, 125, 622, 481, 760, 
    346, 371, 353, 494, 522, 479, 458, 442, 
    432, 441, 439, 480, 440, 364, 338, 383, 
    419, 352, 682, 81, 50, 306, 817
  ) 
  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 (728,96,141,95,149,148,150,133,132,131,94,385,138,137,139,127,128,130,101,146,124,125,622,481,760,346,371,353,494,522,479,458,442,432,441,439,480,440,364,338,383,419,352,682,81,50,306,817)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
50 0.00000000
81 25000.00000000
94 0.00000000
95 0.00000000
96 0.00000000
101 48000.00000000
124 48000.00000000
125 48000.00000000
127 72000.00000000
128 68000.00000000
130 72000.00000000
131 57000.00000000
132 57000.00000000
133 57000.00000000
137 58000.00000000
138 58000.00000000
139 58000.00000000
141 0.00000000
146 48000.00000000
148 62000.00000000
149 62000.00000000
150 62000.00000000
306 168000.00000000
338 32000.00000000
346 0.00000000
352 0.00000000
353 0.00000000
364 50000.00000000
371 0.00000000
383 30000.00000000
385 0.00000000
419 0.00000000
432 0.00000000
439 0.00000000
440 0.00000000
441 0.00000000
442 0.00000000
458 0.00000000
479 0.00000000
480 0.00000000
481 0.00000000
494 0.00000000
522 0.00000000
622 0.00000000
682 0.00000000
728 0.00000000
760 0.00000000
817 0.00000000