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 (
    397, 511, 1023, 1022, 847, 525, 577, 576, 
    658, 659, 932, 656, 657, 376, 540, 592, 
    1015, 767, 1088, 572, 619, 355, 342, 
    547, 591, 902, 323, 757, 1236, 1225, 
    294, 47, 48, 356, 1293, 1242, 846, 593, 
    616, 1087, 520, 548, 899
  ) 
  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.00166

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": 43,
      "filtered": 8.775507927,
      "index_condition": "cscart_product_prices.product_id in (397,511,1023,1022,847,525,577,576,658,659,932,656,657,376,540,592,1015,767,1088,572,619,355,342,547,591,902,323,757,1236,1225,294,47,48,356,1293,1242,846,593,616,1087,520,548,899)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
47 156000.00000000
48 165000.00000000
294 231600.00000000
323 72000.00000000
342 105000.00000000
355 105000.00000000
356 60000.00000000
376 0.00000000
397 0.00000000
511 0.00000000
520 0.00000000
525 0.00000000
540 0.00000000
547 0.00000000
548 0.00000000
572 0.00000000
576 0.00000000
577 128000.00000000
591 0.00000000
592 82000.00000000
593 0.00000000
616 0.00000000
619 105000.00000000
656 0.00000000
657 0.00000000
658 0.00000000
659 0.00000000
757 0.00000000
767 145000.00000000
846 0.00000000
847 0.00000000
899 0.00000000
902 0.00000000
932 0.00000000
1015 145000.00000000
1022 0.00000000
1023 0.00000000
1087 0.00000000
1088 0.00000000
1225 82300.00000000
1236 139000.00000000
1242 490000.00000000
1293 165000.00000000