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 (
    53, 54, 55, 57, 58, 59, 60, 61, 62, 63, 64, 
    65, 66, 69, 70, 71, 72, 73, 74, 75, 76, 
    77, 78, 80, 81, 82, 83, 84, 85, 86, 87, 
    88, 89, 90, 91, 92, 94, 95, 96, 97, 98, 
    99, 100, 101, 102, 103, 104, 105, 106, 
    107, 108, 109, 110, 111, 112, 113, 114, 
    115, 116, 117, 118, 119, 120, 121, 122, 
    123, 124, 125, 126, 127, 128, 129, 130, 
    131, 132, 133, 134, 135, 136, 137, 138, 
    139, 140, 141, 142, 143, 144, 145, 146, 
    147, 148, 149, 150, 151, 152, 154
  ) 
  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.00296

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 (53,54,55,57,58,59,60,61,62,63,64,65,66,69,70,71,72,73,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90,91,92,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,154)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
53 182400.00000000
54 0.00000000
55 57600.00000000
57 44000.00000000
58 44000.00000000
59 44400.00000000
60 28000.00000000
61 25000.00000000
62 69600.00000000
63 78000.00000000
64 0.00000000
65 68000.00000000
66 52000.00000000
69 22000.00000000
70 81000.00000000
71 0.00000000
72 0.00000000
73 0.00000000
74 44000.00000000
75 44000.00000000
76 234000.00000000
77 58000.00000000
78 20000.00000000
80 35000.00000000
81 25000.00000000
82 26400.00000000
83 26400.00000000
84 21000.00000000
85 30000.00000000
86 20000.00000000
87 37000.00000000
88 76000.00000000
89 34000.00000000
90 26400.00000000
91 66000.00000000
92 35000.00000000
94 0.00000000
95 0.00000000
96 0.00000000
97 85909.00000000
98 0.00000000
99 21500.00000000
100 20000.00000000
101 48000.00000000
102 0.00000000
103 0.00000000
104 0.00000000
105 0.00000000
106 0.00000000
107 10000.00000000
108 54000.00000000
109 54000.00000000
110 54000.00000000
111 74400.00000000
112 74400.00000000
113 485000.00000000
114 380000.00000000
115 0.00000000
116 0.00000000
117 0.00000000
118 0.00000000
119 48000.00000000
120 48000.00000000
121 48000.00000000
122 52000.00000000
123 52000.00000000
124 48000.00000000
125 48000.00000000
126 45000.00000000
127 72000.00000000
128 68000.00000000
129 150000.00000000
130 72000.00000000
131 57000.00000000
132 57000.00000000
133 57000.00000000
134 36000.00000000
135 66000.00000000
136 66000.00000000
137 58000.00000000
138 58000.00000000
139 58000.00000000
140 36000.00000000
141 0.00000000
142 57600.00000000
143 57600.00000000
144 85909.00000000
145 85909.00000000
146 48000.00000000
147 48000.00000000
148 62000.00000000
149 62000.00000000
150 62000.00000000
151 56000.00000000
152 45000.00000000
154 103636.00000000