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 (
    403, 1414, 151, 57, 58, 29, 1947, 683, 
    1377, 1166, 358, 1167, 708, 1177, 1195, 
    1208, 1178, 1849, 1846, 1848, 1847, 
    1171, 1322, 1448, 789, 1375, 1230, 1227, 
    1770, 1769, 1420, 77, 1449, 1235, 1442, 
    1958, 67, 1252, 803, 1169, 1712, 1759, 
    1341, 1592, 1474, 1733, 1002, 1930
  ) 
  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.00179

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": 54,
      "filtered": 11.73469067,
      "index_condition": "cscart_product_prices.product_id in (403,1414,151,57,58,29,1947,683,1377,1166,358,1167,708,1177,1195,1208,1178,1849,1846,1848,1847,1171,1322,1448,789,1375,1230,1227,1770,1769,1420,77,1449,1235,1442,1958,67,1252,803,1169,1712,1759,1341,1592,1474,1733,1002,1930)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
29 44000.00000000
57 44000.00000000
58 44000.00000000
67 48000.00000000
77 58000.00000000
151 56000.00000000
358 48000.00000000
403 23000.00000000
683 138000.00000000
708 48000.00000000
789 59000.00000000
803 177000.00000000
1002 35000.00000000
1166 0.00000000
1167 48000.00000000
1169 170000.00000000
1171 88000.00000000
1177 48000.00000000
1178 60000.00000000
1195 60000.00000000
1208 58000.00000000
1227 125000.00000000
1230 45000.00000000
1235 108000.00000000
1252 37000.00000000
1322 52000.00000000
1341 119000.00000000
1375 28000.00000000
1377 0.00000000
1414 21000.00000000
1420 132000.00000000
1442 108000.00000000
1448 0.00000000
1449 44000.00000000
1474 112000.00000000
1592 95000.00000000
1712 27000.00000000
1733 112000.00000000
1759 27000.00000000
1769 17000.00000000
1770 1000.00000000
1846 62000.00000000
1847 62000.00000000
1848 63000.00000000
1849 63000.00000000
1930 23000.00000000
1947 100000.00000000
1958 0.00000000