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 (
    6780, 6689, 6769, 6686, 6688, 6785, 6690, 
    6670, 6799, 6687, 6773, 6795, 6777, 
    6784, 6691, 6807, 6770, 6781, 6783, 
    6776, 6797, 6774, 6790, 7315, 6746, 
    6808, 6731, 6792, 6732, 6744, 6756, 
    6782, 6673, 6714, 6789, 6796, 6694, 
    6778, 6793, 6674, 6718, 6816, 6775, 
    6809, 6715, 6758, 6766, 6768, 6717, 
    6771, 6813, 6716, 6772, 6669, 6671, 
    6812, 6761, 6672, 6760, 6767, 6815, 
    6759, 6668, 6762, 6765, 6810, 6757, 
    6764, 6811, 6763, 6814
  ) 
  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.00339

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "49.71"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 71,
        "rows_produced_per_join": 14,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (6780,6689,6769,6686,6688,6785,6690,6670,6799,6687,6773,6795,6777,6784,6691,6807,6770,6781,6783,6776,6797,6774,6790,7315,6746,6808,6731,6792,6732,6744,6756,6782,6673,6714,6789,6796,6694,6778,6793,6674,6718,6816,6775,6809,6715,6758,6766,6768,6717,6771,6813,6716,6772,6669,6671,6812,6761,6672,6760,6767,6815,6759,6668,6762,6765,6810,6757,6764,6811,6763,6814))",
        "cost_info": {
          "read_cost": "48.29",
          "eval_cost": "1.42",
          "prefix_cost": "49.71",
          "data_read_per_join": "340"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
6668 41000.00000000
6669 86000.00000000
6670 149000.00000000
6671 121000.00000000
6672 60000.00000000
6673 53000.00000000
6674 53000.00000000
6686 298000.00000000
6687 148000.00000000
6688 165000.00000000
6689 146000.00000000
6690 350000.00000000
6691 67000.00000000
6694 76000.00000000
6714 219000.00000000
6715 250000.00000000
6716 314000.00000000
6717 219000.00000000
6718 210000.00000000
6731 151000.00000000
6732 184000.00000000
6744 375000.00000000
6746 347000.00000000
6756 250000.00000000
6757 376000.00000000
6758 289000.00000000
6759 144000.00000000
6760 179000.00000000
6761 72000.00000000
6762 65000.00000000
6763 67000.00000000
6764 104000.00000000
6765 42000.00000000
6766 184000.00000000
6767 158000.00000000
6768 144000.00000000
6769 245000.00000000
6770 83000.00000000
6771 175000.00000000
6772 140000.00000000
6773 86000.00000000
6774 100000.00000000
6775 140000.00000000
6776 97000.00000000
6777 105000.00000000
6778 95000.00000000
6780 125000.00000000
6781 39000.00000000
6782 42000.00000000
6783 51000.00000000
6784 58000.00000000
6785 85000.00000000
6789 84000.00000000
6790 56000.00000000
6792 100000.00000000
6793 114000.00000000
6795 229000.00000000
6796 70000.00000000
6797 79000.00000000
6799 85000.00000000
6807 154000.00000000
6808 51000.00000000
6809 86000.00000000
6810 182000.00000000
6811 167000.00000000
6812 65000.00000000
6813 49000.00000000
6814 44000.00000000
6815 34000.00000000
6816 95000.00000000
7315 39000.00000000