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 (
    1273, 1134, 607, 1092, 584, 1132, 446, 
    492, 461, 399, 649, 1129, 930, 931, 977, 
    929, 1020, 928, 749, 40, 416, 405, 427, 
    444, 827, 826, 595, 485, 486, 838, 549, 
    660, 546, 1260, 978, 979, 401, 400, 457, 
    462, 516, 564, 321, 989, 987, 988, 748, 
    502
  ) 
  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.00176

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 (1273,1134,607,1092,584,1132,446,492,461,399,649,1129,930,931,977,929,1020,928,749,40,416,405,427,444,827,826,595,485,486,838,549,660,546,1260,978,979,401,400,457,462,516,564,321,989,987,988,748,502)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
40 58000.00000000
321 80000.00000000
399 0.00000000
400 0.00000000
401 0.00000000
405 0.00000000
416 65000.00000000
427 0.00000000
444 38000.00000000
446 0.00000000
457 0.00000000
461 0.00000000
462 90000.00000000
485 0.00000000
486 0.00000000
492 0.00000000
502 0.00000000
516 0.00000000
546 72000.00000000
549 93600.00000000
564 0.00000000
584 0.00000000
595 88800.00000000
607 0.00000000
649 0.00000000
660 0.00000000
748 0.00000000
749 0.00000000
826 65000.00000000
827 78000.00000000
838 0.00000000
928 0.00000000
929 0.00000000
930 0.00000000
931 0.00000000
977 0.00000000
978 0.00000000
979 0.00000000
987 70000.00000000
988 70000.00000000
989 70000.00000000
1020 0.00000000
1092 0.00000000
1129 72000.00000000
1132 0.00000000
1134 70000.00000000
1260 230000.00000000
1273 190000.00000000