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 (
    639, 640, 641, 642, 643, 644, 645, 646, 
    647, 648, 649, 650, 651, 652, 653, 654, 
    655, 656, 657, 658, 659, 660, 661, 662, 
    663, 664, 665, 666, 667, 668, 669, 670, 
    671, 672, 673, 674, 675, 676, 677, 678, 
    679, 680, 681, 682, 683, 684, 685, 686, 
    687, 688, 689, 690, 691, 692, 693, 694, 
    695, 696, 697, 698, 699, 700, 701, 702, 
    703, 704, 705, 706, 707, 708, 709, 710, 
    711, 712, 713, 714, 715, 716, 717, 718, 
    719, 720, 721, 722, 723, 724, 725, 726, 
    727, 728, 729, 730, 731, 732, 733, 734
  ) 
  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.00298

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 (639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
639 0.00000000
640 0.00000000
641 0.00000000
642 0.00000000
643 0.00000000
644 0.00000000
645 0.00000000
646 0.00000000
647 0.00000000
648 0.00000000
649 0.00000000
650 0.00000000
651 0.00000000
652 0.00000000
653 144000.00000000
654 0.00000000
655 0.00000000
656 0.00000000
657 0.00000000
658 0.00000000
659 0.00000000
660 0.00000000
661 27600.00000000
662 27600.00000000
663 27600.00000000
664 0.00000000
665 35000.00000000
666 0.00000000
667 0.00000000
668 0.00000000
669 0.00000000
670 0.00000000
671 0.00000000
672 300000.00000000
673 200608.00000000
674 0.00000000
675 0.00000000
676 0.00000000
677 0.00000000
678 0.00000000
679 0.00000000
680 0.00000000
681 35000.00000000
682 0.00000000
683 138000.00000000
684 0.00000000
685 0.00000000
686 0.00000000
687 35000.00000000
688 33000.00000000
689 35000.00000000
690 0.00000000
691 0.00000000
692 0.00000000
693 0.00000000
694 52000.00000000
695 53000.00000000
696 32000.00000000
697 32000.00000000
698 0.00000000
699 103000.00000000
700 0.00000000
701 113000.00000000
702 0.00000000
703 113000.00000000
704 0.00000000
705 0.00000000
706 105000.00000000
707 0.00000000
708 48000.00000000
709 0.00000000
710 110000.00000000
711 110000.00000000
712 0.00000000
713 250000.00000000
714 22000.00000000
715 27000.00000000
716 162000.00000000
717 118000.00000000
718 0.00000000
719 163200.00000000
720 0.00000000
721 0.00000000
722 0.00000000
723 0.00000000
724 0.00000000
725 66000.00000000
726 28000.00000000
727 43000.00000000
728 0.00000000
729 42000.00000000
730 40000.00000000
731 45600.00000000
732 42000.00000000
733 32000.00000000
734 40000.00000000