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 (
    627, 927, 864, 470, 916, 638, 606, 560, 
    625, 1018, 787, 476, 652, 1128, 884, 
    918, 1122, 1125, 1126, 473, 513, 1094, 
    398, 412, 422, 443, 406, 455, 917, 888, 
    618, 636, 553, 429, 901, 459, 410, 552, 
    551, 842, 1123, 912, 910, 911, 913, 914, 
    915, 967
  ) 
  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.00175

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 (627,927,864,470,916,638,606,560,625,1018,787,476,652,1128,884,918,1122,1125,1126,473,513,1094,398,412,422,443,406,455,917,888,618,636,553,429,901,459,410,552,551,842,1123,912,910,911,913,914,915,967)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
398 0.00000000
406 0.00000000
410 0.00000000
412 0.00000000
422 0.00000000
429 0.00000000
443 0.00000000
455 0.00000000
459 0.00000000
470 0.00000000
473 0.00000000
476 0.00000000
513 0.00000000
551 0.00000000
552 0.00000000
553 0.00000000
560 0.00000000
606 0.00000000
618 0.00000000
625 0.00000000
627 0.00000000
636 0.00000000
638 0.00000000
652 0.00000000
787 0.00000000
842 120000.00000000
864 0.00000000
884 0.00000000
888 0.00000000
901 0.00000000
910 0.00000000
911 0.00000000
912 0.00000000
913 0.00000000
914 0.00000000
915 0.00000000
916 0.00000000
917 0.00000000
918 0.00000000
927 0.00000000
967 0.00000000
1018 0.00000000
1094 0.00000000
1122 0.00000000
1123 0.00000000
1125 0.00000000
1126 0.00000000
1128 0.00000000