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 (
    12551, 
    12544, 
    12546, 
    12547, 
    12548, 
    12549, 
    12550, 
    12555, 
    12556, 
    12557, 
    12558, 
    12559, 
    12576, 
    12577, 
    12578, 
    9683, 
    9685, 
    9686, 
    9687, 
    9688, 
    9689, 
    9690, 
    12560, 
    12561, 
    9775, 
    9776, 
    9777, 
    9778, 
    9779, 
    9780, 
    9781, 
    9782, 
    9783, 
    9784, 
    9785, 
    9786, 
    9787, 
    9788, 
    9790, 
    9791, 
    9792, 
    9795, 
    9797, 
    9798, 
    9799, 
    9800, 
    9801, 
    9802, 
    9803, 
    9804, 
    9805, 
    9806, 
    9807, 
    9808, 
    9809, 
    9810, 
    9811, 
    9812, 
    9814, 
    9815, 
    9816, 
    9817, 
    9818, 
    9820, 
    9821, 
    9822, 
    9823, 
    9824, 
    9825, 
    9826, 
    9827, 
    9828, 
    9829, 
    9830, 
    15732, 
    9838, 
    9839, 
    9840, 
    9841, 
    9842, 
    9843, 
    9844, 
    9845, 
    9846, 
    9847, 
    9848, 
    9849, 
    9850, 
    9851, 
    9852, 
    9853, 
    9854, 
    9855, 
    9856, 
    9857, 
    9858, 
    9859, 
    9860, 
    9861, 
    9862, 
    9863, 
    9876, 
    9877, 
    9878, 
    9880, 
    9881, 
    9882, 
    9883, 
    9885, 
    9886, 
    9888, 
    9889, 
    9890, 
    9891, 
    9892, 
    9893, 
    9894, 
    9895, 
    9896, 
    9897, 
    9899, 
    9900, 
    9901, 
    9903, 
    9972, 
    9973, 
    9975, 
    9976
  ) 
  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.00152

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": 128,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (12551,12544,12546,12547,12548,12549,12550,12555,12556,12557,12558,12559,12576,12577,12578,9683,9685,9686,9687,9688,9689,9690,12560,12561,9775,9776,9777,9778,9779,9780,9781,9782,9783,9784,9785,9786,9787,9788,9790,9791,9792,9795,9797,9798,9799,9800,9801,9802,9803,9804,9805,9806,9807,9808,9809,9810,9811,9812,9814,9815,9816,9817,9818,9820,9821,9822,9823,9824,9825,9826,9827,9828,9829,9830,15732,9838,9839,9840,9841,9842,9843,9844,9845,9846,9847,9848,9849,9850,9851,9852,9853,9854,9855,9856,9857,9858,9859,9860,9861,9862,9863,9876,9877,9878,9880,9881,9882,9883,9885,9886,9888,9889,9890,9891,9892,9893,9894,9895,9896,9897,9899,9900,9901,9903,9972,9973,9975,9976)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9683 4000.00000000
9685 4000.00000000
9686 4000.00000000
9687 4000.00000000
9688 4000.00000000
9689 4000.00000000
9690 4000.00000000
9775 1292.58000000
9776 1292.58000000
9777 1292.58000000
9778 1292.58000000
9779 1292.58000000
9780 1292.58000000
9781 1292.58000000
9782 1292.58000000
9783 1292.58000000
9784 1292.58000000
9785 1292.58000000
9786 1292.58000000
9787 1292.58000000
9788 1292.58000000
9790 1292.58000000
9791 1292.58000000
9792 1292.58000000
9795 1292.58000000
9797 1292.58000000
9798 1292.58000000
9799 1292.58000000
9800 1292.58000000
9801 1292.58000000
9802 1292.58000000
9803 1292.58000000
9804 1292.58000000
9805 1292.58000000
9806 1292.58000000
9807 1292.58000000
9808 1292.58000000
9809 1292.58000000
9810 1292.58000000
9811 1292.58000000
9812 1292.58000000
9814 1136.58000000
9815 1136.58000000
9816 1136.58000000
9817 1136.58000000
9818 1136.58000000
9820 1136.58000000
9821 1136.58000000
9822 1136.58000000
9823 1136.58000000
9824 1136.58000000
9825 1136.58000000
9826 1136.58000000
9827 1136.58000000
9828 1136.58000000
9829 1136.58000000
9830 1136.58000000
9838 2150.58000000
9839 2150.58000000
9840 2150.58000000
9841 2150.58000000
9842 2150.58000000
9843 2150.58000000
9844 2150.58000000
9845 2150.58000000
9846 2150.58000000
9847 2150.58000000
9848 2150.58000000
9849 2150.58000000
9850 2150.58000000
9851 2150.58000000
9852 2150.58000000
9853 2150.58000000
9854 2150.58000000
9855 2150.58000000
9856 2150.58000000
9857 2150.58000000
9858 2150.58000000
9859 2150.58000000
9860 2150.58000000
9861 2150.58000000
9862 2150.58000000
9863 2150.58000000
9876 1036.29000000
9877 1036.29000000
9878 1036.29000000
9880 1036.29000000
9881 1036.29000000
9882 1036.29000000
9883 1036.29000000
9885 1036.29000000
9886 1036.29000000
9888 1036.29000000
9889 1036.29000000
9890 1036.29000000
9891 1036.29000000
9892 1036.29000000
9893 1036.29000000
9894 1036.29000000
9895 1036.29000000
9896 1036.29000000
9897 1036.29000000
9899 1036.29000000
9900 1036.29000000
9901 1036.29000000
9903 1036.29000000
9972 582.40000000
9973 582.40000000
9975 582.40000000
9976 582.40000000
12544 41728.72000000
12546 67629.15000000
12547 67629.15000000
12548 52280.84000000
12549 43647.16000000
12550 47964.00000000
12551 15215.00000000
12555 41728.72000000
12556 41728.72000000
12557 52280.84000000
12558 82018.56000000
12559 48683.54000000
12560 67629.15000000
12561 67629.15000000
12576 41728.72000000
12577 42208.28000000
12578 36540.00000000
15732 1136.58000000