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 (
    417, 433, 580, 514, 471, 654, 263, 1037, 
    574, 495, 813, 1086, 844, 756, 755, 434, 
    863, 220, 823, 610, 867, 858, 635, 1044, 
    579, 578, 1127, 1042, 868, 517, 938, 
    946, 456, 559, 542, 407, 866, 515, 561, 
    491, 493, 573, 651, 562, 435, 523, 650, 
    583
  ) 
  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.00188

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (417,433,580,514,471,654,263,1037,574,495,813,1086,844,756,755,434,863,220,823,610,867,858,635,1044,579,578,1127,1042,868,517,938,946,456,559,542,407,866,515,561,491,493,573,651,562,435,523,650,583)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
220 0.00000000
263 1560000.00000000
407 0.00000000
417 0.00000000
433 0.00000000
434 0.00000000
435 0.00000000
456 0.00000000
471 0.00000000
491 0.00000000
493 0.00000000
495 1656000.00000000
514 0.00000000
515 0.00000000
517 0.00000000
523 0.00000000
542 0.00000000
559 0.00000000
561 0.00000000
562 114000.00000000
573 0.00000000
574 0.00000000
578 384000.00000000
579 105000.00000000
580 420000.00000000
583 534000.00000000
610 0.00000000
635 0.00000000
650 0.00000000
651 0.00000000
654 0.00000000
755 0.00000000
756 0.00000000
813 354000.00000000
823 0.00000000
844 0.00000000
858 0.00000000
863 0.00000000
866 0.00000000
867 0.00000000
868 0.00000000
938 0.00000000
946 0.00000000
1037 0.00000000
1042 0.00000000
1044 0.00000000
1086 0.00000000
1127 0.00000000