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 (
    802, 207, 205, 206, 201, 204, 203, 202, 
    597, 589, 1750, 713, 466, 535, 423, 534, 
    501, 1493, 531, 465, 588, 644, 640, 646, 
    643, 642, 641, 645, 507, 814, 815, 218, 
    1119, 1120, 1121, 861, 859, 437, 428, 
    843, 862, 1108, 267, 857, 1749, 396, 
    1659, 791, 474, 426, 793, 794, 1147, 
    849, 850, 852, 853, 851, 855, 854, 856, 
    208, 209, 210, 886, 555, 630, 554, 1109, 
    320, 318, 319, 969, 970, 252, 454, 639, 
    49
  ) 
  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.00282

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": 78,
      "filtered": 15.91836357,
      "index_condition": "cscart_product_prices.product_id in (802,207,205,206,201,204,203,202,597,589,1750,713,466,535,423,534,501,1493,531,465,588,644,640,646,643,642,641,645,507,814,815,218,1119,1120,1121,861,859,437,428,843,862,1108,267,857,1749,396,1659,791,474,426,793,794,1147,849,850,852,853,851,855,854,856,208,209,210,886,555,630,554,1109,320,318,319,969,970,252,454,639,49)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
49 69600.00000000
201 0.00000000
202 0.00000000
203 0.00000000
204 0.00000000
205 0.00000000
206 125000.00000000
207 0.00000000
208 0.00000000
209 0.00000000
210 0.00000000
218 0.00000000
252 0.00000000
267 0.00000000
318 240000.00000000
319 240000.00000000
320 240000.00000000
396 410000.00000000
423 250000.00000000
426 350000.00000000
428 516000.00000000
437 0.00000000
454 0.00000000
465 260000.00000000
466 265000.00000000
474 0.00000000
501 260000.00000000
507 0.00000000
531 265000.00000000
534 0.00000000
535 0.00000000
554 0.00000000
555 300000.00000000
588 260000.00000000
589 0.00000000
597 0.00000000
630 0.00000000
639 0.00000000
640 0.00000000
641 0.00000000
642 0.00000000
643 0.00000000
644 0.00000000
645 0.00000000
646 0.00000000
713 250000.00000000
791 375000.00000000
793 252000.00000000
794 180000.00000000
802 0.00000000
814 51360.00000000
815 41000.00000000
843 510000.00000000
849 0.00000000
850 0.00000000
851 0.00000000
852 0.00000000
853 0.00000000
854 0.00000000
855 0.00000000
856 0.00000000
857 552000.00000000
859 696000.00000000
861 540000.00000000
862 576000.00000000
886 0.00000000
969 0.00000000
970 0.00000000
1108 0.00000000
1109 0.00000000
1119 0.00000000
1120 0.00000000
1121 588000.00000000
1147 180000.00000000
1493 260000.00000000
1659 465000.00000000
1749 490000.00000000
1750 260000.00000000