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 (
    802, 207, 205, 206, 201, 204, 203, 202, 
    597, 589, 1750, 713, 466, 535, 423, 534, 
    501, 1493, 531, 465, 588, 644, 640, 646, 
    643, 642, 641, 645, 507, 814, 815, 218, 
    1119, 1120, 1121, 861, 859, 437, 428, 
    843, 862, 1108, 267, 857, 1749, 396, 
    1659, 791
  ) 
  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.00186

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 (802,207,205,206,201,204,203,202,597,589,1750,713,466,535,423,534,501,1493,531,465,588,644,640,646,643,642,641,645,507,814,815,218,1119,1120,1121,861,859,437,428,843,862,1108,267,857,1749,396,1659,791)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
201 0.00000000
202 0.00000000
203 0.00000000
204 0.00000000
205 0.00000000
206 125000.00000000
207 0.00000000
218 0.00000000
267 0.00000000
396 410000.00000000
423 250000.00000000
428 516000.00000000
437 0.00000000
465 260000.00000000
466 265000.00000000
501 260000.00000000
507 0.00000000
531 265000.00000000
534 0.00000000
535 0.00000000
588 260000.00000000
589 0.00000000
597 0.00000000
640 0.00000000
641 0.00000000
642 0.00000000
643 0.00000000
644 0.00000000
645 0.00000000
646 0.00000000
713 250000.00000000
791 375000.00000000
802 0.00000000
814 51360.00000000
815 41000.00000000
843 510000.00000000
857 552000.00000000
859 696000.00000000
861 540000.00000000
862 576000.00000000
1108 0.00000000
1119 0.00000000
1120 0.00000000
1121 588000.00000000
1493 260000.00000000
1659 465000.00000000
1749 490000.00000000
1750 260000.00000000