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 (
    278, 280, 282, 247, 248, 241, 244, 245, 
    246, 240, 6, 66, 166, 214, 219, 221, 223, 
    192, 194, 195, 196, 197, 198, 199, 200, 
    201, 202, 203, 204, 205, 206, 207, 208, 
    209, 210, 211, 212, 213, 215, 217, 218, 
    220, 222, 224, 225, 226, 227, 228
  ) 
  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.00007

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": 15.18447495,
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (278,280,282,247,248,241,244,245,246,240,6,66,166,214,219,221,223,192,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,215,217,218,220,222,224,225,226,227,228) and cscart_product_prices.usergroup_id in (0,1)"
        }
      }
    }
  }
}

Result

product_id price
6 39600.000000
66 46800.000000
166 90000.000000
192 1800.000000
194 1320.000000
195 3600.000000
196 2040.000000
197 1800.000000
198 2160.000000
199 3600.000000
200 3240.000000
201 1560.000000
202 4200.000000
203 4200.000000
204 1800.000000
205 18000.000000
206 21600.000000
207 5040.000000
208 9960.000000
209 13200.000000
210 10800.000000
211 36000.000000
212 15600.000000
213 35400.000000
214 116640.000000
215 131400.000000
217 73320.000000
218 55200.000000
219 63600.000000
220 132000.000000
221 245880.000000
222 63600.000000
223 60000.000000
224 57600.000000
225 24000.000000
226 32400.000000
227 83880.000000
228 42000.000000
240 59880.000000
241 59880.000000
244 87600.000000
245 699.000000
246 48000.000000
247 39540.000000
248 44680.000000
278 3000.000000
280 3600.000000
282 2799.000000