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 (
    145, 200, 201, 202, 203, 204, 205, 206, 
    207, 208, 209, 190, 210, 211, 212, 213, 
    214, 215, 216, 217, 218, 219, 191, 220, 
    221, 222, 223, 224, 225, 226, 227, 228, 
    229, 192, 230, 231, 232, 233, 234, 235, 
    236, 237, 238, 239, 193, 240, 241, 242, 
    243, 194, 195, 198, 199, 77, 82, 84, 85, 
    83, 87, 89, 164, 142, 92, 93
  ) 
  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.00185

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": "usergroup",
      "key_length": "9",
      "used_key_parts": ["product_id", "usergroup_id", "lower_limit"],
      "rows": 126,
      "filtered": 100,
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (145,200,201,202,203,204,205,206,207,208,209,190,210,211,212,213,214,215,216,217,218,219,191,220,221,222,223,224,225,226,227,228,229,192,230,231,232,233,234,235,236,237,238,239,193,240,241,242,243,194,195,198,199,77,82,84,85,83,87,89,164,142,92,93) and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
77 53333.000000
82 53333.000000
83 53333.000000
84 53333.000000
85 53333.000000
87 10000.000000
89 100.000000
92 53333.000000
93 53333.000000
142 0.000000
145 85714.000000
164 99000.000000
190 0.000000
191 0.000000
192 0.000000
193 0.000000
194 0.000000
195 0.000000
198 0.000000
199 0.000000
200 0.000000
201 0.000000
202 0.000000
203 0.000000
204 0.000000
205 0.000000
206 0.000000
207 0.000000
208 0.000000
209 0.000000
210 0.000000
211 0.000000
212 0.000000
213 0.000000
214 0.000000
215 0.000000
216 0.000000
217 0.000000
218 0.000000
219 0.000000
220 0.000000
221 0.000000
222 0.000000
223 0.000000
224 0.000000
225 0.000000
226 0.000000
227 0.000000
228 0.000000
229 0.000000
230 0.000000
231 0.000000
232 0.000000
233 0.000000
234 0.000000
235 0.000000
236 0.000000
237 0.000000
238 0.000000
239 0.000000
240 0.000000
241 0.000000
242 0.000000
243 0.000000