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 (
    4, 1, 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 
    17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 
    27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 
    37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 
    47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
    57, 58, 59, 60, 62, 63, 64, 65, 67, 68, 
    69, 70, 71, 72, 73, 74, 75, 76, 78, 79, 
    80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 
    90, 91, 92, 93, 94, 95, 96, 97, 100, 101, 
    102, 103, 104
  ) 
  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.00099

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_product_prices.product_id",
      "temporary_table": {
        "table": {
          "table_name": "cscart_product_prices",
          "access_type": "ALL",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id"
          ],
          "rows": 253,
          "filtered": 53.266819,
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (4,1,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,62,63,64,65,67,68,69,70,71,72,73,74,75,76,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,100,101,102,103,104) and cscart_product_prices.usergroup_id in (0,1)"
        }
      }
    }
  }
}

Result

product_id price
1 648000.000000
4 84000.000000
5 108000.000000
7 6600.000000
8 102000.000000
9 120000.000000
10 144000.000000
11 3600.000000
12 3600.000000
13 1440.000000
14 60000.000000
15 18000.000000
16 42000.000000
17 1320.000000
18 36000.000000
19 9600.000000
20 2400.000000
21 3600.000000
22 96000.000000
23 72000.000000
24 54000.000000
25 72000.000000
26 4200.000000
27 6600.000000
28 12000.000000
29 24000.000000
30 42000.000000
31 3840.000000
32 36000.000000
33 20400.000000
34 3000.000000
35 3840.000000
36 6120.000000
37 19200.000000
38 2040.000000
39 50280.000000
40 27480.000000
41 4200.000000
42 9480.000000
43 44280.000000
44 3000.000000
45 8880.000000
46 2520.000000
47 3600.000000
48 21600.000000
49 14400.000000
50 26400.000000
51 21600.000000
52 16800.000000
53 4680.000000
54 32280.000000
55 43080.000000
56 26400.000000
57 37080.000000
58 93480.000000
59 71880.000000
60 179880.000000
62 25200.000000
63 22800.000000
64 22800.000000
65 12000.000000
67 40800.000000
68 96000.000000
69 63600.000000
70 60000.000000
71 63600.000000
72 72000.000000
73 70800.000000
74 63000.000000
75 58800.000000
76 52800.000000
78 12000.000000
79 11520.000000
80 6600.000000
81 6000.000000
82 2400.000000
83 2400.000000
84 2400.000000
85 2400.000000
86 43080.000000
87 2400.000000
88 4800.000000
89 2400.000000
90 2400.000000
91 1284000.000000
92 387000.000000
93 2400.000000
94 7200.000000
95 2400.000000
96 12000.000000
97 1800.000000
100 2760.000000
101 22680.000000
102 35400.000000
103 2880.000000
104 3600.000000