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, 417, 433, 580, 514, 471, 654, 
    263, 1037, 574, 495, 813, 1086, 844, 
    756, 755, 434, 863, 220, 823, 610, 867, 
    858, 635, 1044, 579, 578, 1127, 1042, 
    868, 517, 938, 946, 456, 559, 542, 407, 
    866, 515, 561, 491, 493, 573, 651, 562, 
    435, 523, 650, 583
  ) 
  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.00304

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": 96,
      "filtered": 19.59183121,
      "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,417,433,580,514,471,654,263,1037,574,495,813,1086,844,756,755,434,863,220,823,610,867,858,635,1044,579,578,1127,1042,868,517,938,946,456,559,542,407,866,515,561,491,493,573,651,562,435,523,650,583)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
220 0.00000000
263 1560000.00000000
398 0.00000000
406 0.00000000
407 0.00000000
410 0.00000000
412 0.00000000
417 0.00000000
422 0.00000000
429 0.00000000
433 0.00000000
434 0.00000000
435 0.00000000
443 0.00000000
455 0.00000000
456 0.00000000
459 0.00000000
470 0.00000000
471 0.00000000
473 0.00000000
476 0.00000000
491 0.00000000
493 0.00000000
495 1656000.00000000
513 0.00000000
514 0.00000000
515 0.00000000
517 0.00000000
523 0.00000000
542 0.00000000
551 0.00000000
552 0.00000000
553 0.00000000
559 0.00000000
560 0.00000000
561 0.00000000
562 114000.00000000
573 0.00000000
574 0.00000000
578 384000.00000000
579 105000.00000000
580 420000.00000000
583 534000.00000000
606 0.00000000
610 0.00000000
618 0.00000000
625 0.00000000
627 0.00000000
635 0.00000000
636 0.00000000
638 0.00000000
650 0.00000000
651 0.00000000
652 0.00000000
654 0.00000000
755 0.00000000
756 0.00000000
787 0.00000000
813 354000.00000000
823 0.00000000
842 120000.00000000
844 0.00000000
858 0.00000000
863 0.00000000
864 0.00000000
866 0.00000000
867 0.00000000
868 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
938 0.00000000
946 0.00000000
967 0.00000000
1018 0.00000000
1037 0.00000000
1042 0.00000000
1044 0.00000000
1086 0.00000000
1094 0.00000000
1122 0.00000000
1123 0.00000000
1125 0.00000000
1126 0.00000000
1127 0.00000000
1128 0.00000000