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 (
    453, 
    454, 
    455, 
    450, 
    451, 
    452, 
    435, 
    439, 
    443, 
    430, 
    429, 
    428, 
    415, 
    416, 
    417, 
    418, 
    419, 
    420, 
    421, 
    422, 
    423, 
    424, 
    425, 
    426, 
    403, 
    404, 
    405, 
    406, 
    407, 
    408, 
    409, 
    410, 
    411, 
    412, 
    413, 
    414, 
    391, 
    392, 
    393, 
    394, 
    395, 
    396, 
    397, 
    398, 
    399, 
    400, 
    401, 
    402, 
    379, 
    380, 
    381, 
    382, 
    383, 
    384, 
    385, 
    386, 
    387, 
    388, 
    389, 
    390, 
    363, 
    364, 
    365, 
    366, 
    367, 
    368, 
    369, 
    370, 
    355, 
    356, 
    357, 
    358, 
    359, 
    360, 
    361, 
    362, 
    352, 
    353, 
    354, 
    350, 
    349, 
    346, 
    345, 
    331, 
    335, 
    339, 
    341, 
    315, 
    289, 
    287, 
    286, 
    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
  ) 
  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.00130

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "336.45"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "256.00"
      },
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "ALL",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "rows_examined_per_scan": 383,
        "rows_produced_per_join": 256,
        "filtered": "66.84",
        "cost_info": {
          "read_cost": "29.26",
          "eval_cost": "51.20",
          "prefix_cost": "80.46",
          "data_read_per_join": "6K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`pankajecarter_systemfour`.`cscart_product_prices`.`lower_limit` = 1) and (`pankajecarter_systemfour`.`cscart_product_prices`.`product_id` in (453,454,455,450,451,452,435,439,443,430,429,428,415,416,417,418,419,420,421,422,423,424,425,426,403,404,405,406,407,408,409,410,411,412,413,414,391,392,393,394,395,396,397,398,399,400,401,402,379,380,381,382,383,384,385,386,387,388,389,390,363,364,365,366,367,368,369,370,355,356,357,358,359,360,361,362,352,353,354,350,349,346,345,331,335,339,341,315,289,287,286,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)) and (`pankajecarter_systemfour`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
6 329.99000000
66 389.95000000
166 749.95000000
192 15.00000000
194 10.60000000
195 29.99000000
196 17.00000000
197 14.98000000
198 17.99000000
199 29.98000000
200 26.92000000
201 12.67000000
202 34.68000000
203 34.68000000
204 14.99000000
205 149.99000000
206 179.99000000
207 42.00000000
208 82.94000000
209 109.99000000
210 89.99000000
211 299.99000000
212 129.95000000
214 972.00000000
219 529.99000000
221 2049.00000000
223 499.99000000
240 499.00000000
241 499.00000000
244 729.99000000
245 699.00000000
246 399.99000000
247 329.49000000
248 372.27000000
278 75.00000000
280 50.00000000
282 75.00000000
286 12000.00000000
287 10000.00000000
289 15000.00000000
315 10000.00000000
331 1200.00000000
335 1200.00000000
339 1200.00000000
341 1200.00000000
345 79.95000000
346 79.95000000
349 899.99000000
350 899.99000000
352 199.99000000
353 199.99000000
354 199.99000000
355 10200.00000000
356 10200.00000000
357 1200.00000000
358 1200.00000000
359 1200.00000000
360 1200.00000000
361 1200.00000000
362 1200.00000000
363 10200.00000000
364 10200.00000000
365 1200.00000000
366 1200.00000000
367 1200.00000000
368 1200.00000000
369 1200.00000000
370 1200.00000000
379 1200.00000000
380 1200.00000000
381 1200.00000000
382 1200.00000000
383 1200.00000000
384 1200.00000000
385 1200.00000000
386 1200.00000000
387 1200.00000000
388 1200.00000000
389 1200.00000000
390 1200.00000000
391 1200.00000000
392 1200.00000000
393 1200.00000000
394 1200.00000000
395 1200.00000000
396 1200.00000000
397 1200.00000000
398 1200.00000000
399 1200.00000000
400 1200.00000000
401 1200.00000000
402 1200.00000000
403 1200.00000000
404 1200.00000000
405 1200.00000000
406 1200.00000000
407 1200.00000000
408 1200.00000000
409 1200.00000000
410 1200.00000000
411 1200.00000000
412 1200.00000000
413 1200.00000000
414 1200.00000000
415 1200.00000000
416 1200.00000000
417 1200.00000000
418 1200.00000000
419 1200.00000000
420 1200.00000000
421 1200.00000000
422 1200.00000000
423 1200.00000000
424 1200.00000000
425 1200.00000000
426 1200.00000000
428 899.99000000
429 99.00000000
430 99.00000000
435 79.95000000
439 79.95000000
443 79.95000000
450 5399.99000000
451 5399.99000000
452 5399.99000000
453 5399.99000000
454 5399.99000000
455 5399.99000000